<!DOCTYPE html>


  <html class="dark page-post">


<head><meta name="generator" content="Hexo 3.9.0">
  <meta charset="utf-8">
  
  <title>Mysql基础复习 | Poetry&#39;s Blog</title>

  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">

  
    <meta name="keywords" content="Mysql,">
  

  <meta name="description" content="好久没用sql，都忘得干干净净，翻阅以前的学习笔记，觉得有些可记录的点，放在这里以便备用查阅  一、环境搭建 mac安装MySQL  brew install mysql  # 启动mysql.server start #登录mysql -uroot 二、基础知识1、数据库的连接# 例子mysql -u root -p 123456 -h 127.0.0.1  -u 用户名 -p 密码 -h h">
<meta name="keywords" content="Mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql基础复习">
<meta property="og:url" content="http://blog.poetries.top/2019/01/22/mysql-base-review/index.html">
<meta property="og:site_name" content="Poetry&#39;s Blog">
<meta property="og:description" content="好久没用sql，都忘得干干净净，翻阅以前的学习笔记，觉得有些可记录的点，放在这里以便备用查阅  一、环境搭建 mac安装MySQL  brew install mysql  # 启动mysql.server start #登录mysql -uroot 二、基础知识1、数据库的连接# 例子mysql -u root -p 123456 -h 127.0.0.1  -u 用户名 -p 密码 -h h">
<meta property="og:locale" content="zh-Hans">
<meta property="og:image" content="https://poetries1.gitee.io/img-repo/2019/10/355.png">
<meta property="og:updated_time" content="2020-08-15T04:25:31.926Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql基础复习">
<meta name="twitter:description" content="好久没用sql，都忘得干干净净，翻阅以前的学习笔记，觉得有些可记录的点，放在这里以便备用查阅  一、环境搭建 mac安装MySQL  brew install mysql  # 启动mysql.server start #登录mysql -uroot 二、基础知识1、数据库的连接# 例子mysql -u root -p 123456 -h 127.0.0.1  -u 用户名 -p 密码 -h h">
<meta name="twitter:image" content="https://poetries1.gitee.io/img-repo/2019/10/355.png">

  

  
    <link rel="icon" href="/favicon.ico">
  

  <link href="/css/styles.css?v=c114cbeddx" rel="stylesheet">
<link href="/css/other.css?v=c114cbeddx" rel="stylesheet">


  
    <link rel="stylesheet" href="/css/personal-style.css">
  

  

  
  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?40b1f89aa80f2527b3db779c6898c879";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>


  
  <script type="text/javascript">
	(function(){
	    var bp = document.createElement('script');
	    var curProtocol = window.location.protocol.split(':')[0];
	    if (curProtocol === 'https') {
	        bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';        
	    }
	    else {
	        bp.src = 'http://push.zhanzhang.baidu.com/push.js';
	    }
	    var s = document.getElementsByTagName("script")[0];
	    s.parentNode.insertBefore(bp, s);
	})();
  </script>



  
    <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <link rel="stylesheet" href="//cdn.bootcss.com/font-awesome/4.3.0/css/font-awesome.min.css">
  

  <!-- 聊天系统 -->
  
    
   <link type="text/css" rel="stylesheet" href="/renxi/default.css">
   <style>
      #modal {
        position: static !important;
      }
      .filter {
        width: 100%;
        height: 100%;
        position: absolute;
        top: 0;
        left: 0;
        background: #fe5757;
        animation: colorChange 30s ease-in-out infinite;
        animation-fill-mode: both;
        mix-blend-mode: overlay;
      }
  
      @keyframes colorChange {
        0%, 100% {
            opacity: 0;
        }
        50% {
            opacity: .9;
        }
      }
   </style>
</head>
</html>
<body>
  
  
    <span id="toolbox-mobile" class="toolbox-mobile">导航</span>
  

  <div class="post-header CENTER">
   
  <div class="toolbox">
    <a class="toolbox-entry" href="/">
      <span class="toolbox-entry-text">导航</span>
      <i class="icon-angle-down"></i>
      <i class="icon-home"></i>
    </a>
    <ul class="list-toolbox">
      
        <li class="item-toolbox">
          <a
            class="CIRCLE"
            href="/archives/"
            rel="noopener noreferrer"
            target="_self"
            >
            博客
          </a>
        </li>
      
        <li class="item-toolbox">
          <a
            class="CIRCLE"
            href="/categories/"
            rel="noopener noreferrer"
            target="_self"
            >
            分类
          </a>
        </li>
      
        <li class="item-toolbox">
          <a
            class="CIRCLE"
            href="/tags/"
            rel="noopener noreferrer"
            target="_self"
            >
            标签
          </a>
        </li>
      
        <li class="item-toolbox">
          <a
            class="CIRCLE"
            href="/search/"
            rel="noopener noreferrer"
            target="_self"
            >
            搜索
          </a>
        </li>
      
        <li class="item-toolbox">
          <a
            class="CIRCLE"
            href="/link/"
            rel="noopener noreferrer"
            target="_self"
            >
            友链
          </a>
        </li>
      
        <li class="item-toolbox">
          <a
            class="CIRCLE"
            href="/about/"
            rel="noopener noreferrer"
            target="_self"
            >
            关于
          </a>
        </li>
      
    </ul>
  </div>


</div>


  <div id="toc" class="toc-article">
    <strong class="toc-title">文章目录<i class="iconfont toc-title" style="display:inline-block;color:#87998d;width:20px;height:20px;">&#xf004b;</i></strong>
    <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#一、环境搭建"><span class="toc-text">一、环境搭建</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#二、基础知识"><span class="toc-text">二、基础知识</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1、数据库的连接"><span class="toc-text">1、数据库的连接</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2、库级知识"><span class="toc-text">2、库级知识</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3、表级操作"><span class="toc-text">3、表级操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-显示库下面的表"><span class="toc-text">3.1 显示库下面的表</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-查看表的结构"><span class="toc-text">3.2 查看表的结构</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-查看表的创建过程"><span class="toc-text">3.3 查看表的创建过程:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-4-创建表"><span class="toc-text">3.4 创建表</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-5-修改表"><span class="toc-text">3.5 修改表</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4、列类型讲解"><span class="toc-text">4、列类型讲解</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#4-1-列类型"><span class="toc-text">4.1 列类型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-2-数值型"><span class="toc-text">4.2 数值型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-3-字符型"><span class="toc-text">4.3 字符型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-4-日期时间类型"><span class="toc-text">4.4 日期时间类型</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5、增删改查基本操作"><span class="toc-text">5、增删改查基本操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#5-1-插入数据"><span class="toc-text">5.1 插入数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-2-修改数据"><span class="toc-text">5.2 修改数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-3-删除数据"><span class="toc-text">5.3 删除数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-4-select-查询"><span class="toc-text">5.4 select 查询</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6、连接查询"><span class="toc-text">6、连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#6-1-左连接"><span class="toc-text">6.1 左连接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#6-2-右链接"><span class="toc-text">6.2 右链接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#6-3-内连接"><span class="toc-text">6.3 内连接</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7、子查询"><span class="toc-text">7、子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8、字符集"><span class="toc-text">8、字符集</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#三、查询知识"><span class="toc-text">三、查询知识</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#3-1-基础查询-where的练习"><span class="toc-text">3.1 基础查询 where的练习</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-1-主键为32的商品"><span class="toc-text">3.1.1 主键为32的商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-2-不属第3栏目的所有商品"><span class="toc-text">3.1.2 不属第3栏目的所有商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-3-本店价格高于3000元的商品"><span class="toc-text">3.1.3 本店价格高于3000元的商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-4-本店价格低于或等于100元的商品"><span class="toc-text">3.1.4 本店价格低于或等于100元的商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-5-取出第4栏目或第11栏目的商品-不许用or"><span class="toc-text">3.1.5 取出第4栏目或第11栏目的商品(不许用or)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-6-取出100-lt-价格-lt-500的商品-不许用and"><span class="toc-text">3.1.6 取出100&lt;=价格&lt;=500的商品(不许用and)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-7-取出不属于第3栏目且不属于第11栏目的商品-and-或not-in分别实现"><span class="toc-text">3.1.7 取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-8-取出价格大于100且小于300-或者大于4000且小于5000的商品"><span class="toc-text">3.1.8 取出价格大于100且小于300,或者大于4000且小于5000的商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-9-取出第3个栏目下面价格-lt-1000或-gt-3000-并且点击量-gt-5的系列商品"><span class="toc-text">3.1.9 取出第3个栏目下面价格&lt;1000或&gt;3000,并且点击量&gt;5的系列商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-10-取出第1个栏目下面的商品-注意-1栏目下面没商品-但其子栏目下有"><span class="toc-text">3.1.10 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-11-取出名字以”诺基亚”开头的商品"><span class="toc-text">3.1.11 取出名字以”诺基亚”开头的商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-12-取出名字为”诺基亚Nxx”的手机"><span class="toc-text">3.1.12 取出名字为”诺基亚Nxx”的手机</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-13-取出名字不以”诺基亚”开头的商品"><span class="toc-text">3.1.13 取出名字不以”诺基亚”开头的商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-14-取出第3个栏目下面价格在1000到3000之间-并且点击量-gt-5-“诺基亚”开头的系列商品"><span class="toc-text">3.1.14 取出第3个栏目下面价格在1000到3000之间,并且点击量&gt;5 “诺基亚”开头的系列商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-15-一道面试题"><span class="toc-text">3.1.15 一道面试题</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-1-16-练习题"><span class="toc-text">3.1.16 练习题:</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-2-分组查询group"><span class="toc-text">3.2    分组查询group</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-1-查出最贵的商品的价格"><span class="toc-text">3.2.1 查出最贵的商品的价格</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-2-查出最大-最新-的商品编号"><span class="toc-text">3.2.2 查出最大(最新)的商品编号</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-3-查出最便宜的商品的价格"><span class="toc-text">3.2.3 查出最便宜的商品的价格</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-4-查出最旧-最小-的商品编号"><span class="toc-text">3.2.4 查出最旧(最小)的商品编号</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-5-查询该店所有商品的库存总量"><span class="toc-text">3.2.5 查询该店所有商品的库存总量</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-6-查询所有商品的平均价"><span class="toc-text">3.2.6 查询所有商品的平均价</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-7-查询该店一共有多少种商品"><span class="toc-text">3.2.7 查询该店一共有多少种商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-2-8-查询每个栏目下面"><span class="toc-text">3.2.8 查询每个栏目下面</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-3-having与group综合运用查询"><span class="toc-text">3.3 having与group综合运用查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-1-查询该店的商品比市场价所节省的价格"><span class="toc-text">3.3.1 查询该店的商品比市场价所节省的价格</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-2-查询每个商品所积压的货款-提示-库存-单价"><span class="toc-text">3.3.2 查询每个商品所积压的货款(提示:库存*单价)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-3-查询该店积压的总货款"><span class="toc-text">3.3.3 查询该店积压的总货款</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-4-查询该店每个栏目下面积压的货款"><span class="toc-text">3.3.4 查询该店每个栏目下面积压的货款.</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-5-查询比市场价省钱200元以上的商品及该商品所省的钱-where和having分别实现"><span class="toc-text">3.3.5 查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-6-查询积压货款超过2W元的栏目-以及该栏目积压的货款"><span class="toc-text">3.3.6 查询积压货款超过2W元的栏目,以及该栏目积压的货款</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-3-7-where-having-group综合练习题"><span class="toc-text">3. 3.7 where-having-group综合练习题</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-4、order-by-与-limit查询"><span class="toc-text">3.4、order by 与 limit查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-4-1-按价格由高到低排序"><span class="toc-text">3.4.1 按价格由高到低排序</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-4-2-按发布时间由早到晚排序"><span class="toc-text">3.4.2 按发布时间由早到晚排序</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-4-3-接栏目由低到高排序-栏目内部按价格由高到低排序"><span class="toc-text">3.4.3 接栏目由低到高排序,栏目内部按价格由高到低排序</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-4-4-取出价格最高的前三名商品"><span class="toc-text">3.4.4 取出价格最高的前三名商品</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-4-5-取出点击量前三名到前5名的商品"><span class="toc-text">3.4.5 取出点击量前三名到前5名的商品</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-5-连接查询"><span class="toc-text">3.5    连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#3-5-1-取出所有商品的商品名-栏目名-价格"><span class="toc-text">3.5.1 取出所有商品的商品名,栏目名,价格</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-5-2-取出第4个栏目下的商品的商品名-栏目名-价格"><span class="toc-text">3.5.2 取出第4个栏目下的商品的商品名,栏目名,价格</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-5-3-取出第4个栏目下的商品的商品名-栏目名-与品牌名"><span class="toc-text">3.5.3 取出第4个栏目下的商品的商品名,栏目名,与品牌名</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-5-4-面试题"><span class="toc-text">3.5.4 面试题</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-6、union查询"><span class="toc-text">3.6、union查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-7、子查询"><span class="toc-text">3.7、子查询:</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#四、常用表管理语句"><span class="toc-text">四、常用表管理语句</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#五、查询总结"><span class="toc-text">五、查询总结</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#5-1-insert"><span class="toc-text">5.1 insert</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-2-update操作"><span class="toc-text">5.2 update操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-3-delete操作"><span class="toc-text">5.3 delete操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-4-select查找"><span class="toc-text">5.4 select查找</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-5-select查询模型（重要）"><span class="toc-text">5.5 select查询模型（重要）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-6-limit用法（做分页类能用到）"><span class="toc-text">5.6 limit用法（做分页类能用到）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-7-子句的查询陷阱"><span class="toc-text">5.7 子句的查询陷阱</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-8-子查询"><span class="toc-text">5.8 子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-9-from子查询"><span class="toc-text">5.9 from子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-10-exists子查询："><span class="toc-text">5.10 exists子查询：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-11-内连接查询（重要）"><span class="toc-text">5.11 内连接查询（重要）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-12-左连接特点"><span class="toc-text">5.12 左连接特点</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-13-union查询"><span class="toc-text">5.13 union查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-1-整型列"><span class="toc-text">6.1 整型列</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-2-浮点列与定点列"><span class="toc-text">6.2 浮点列与定点列</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-3-字符型列"><span class="toc-text">6.3 字符型列</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-4-日期时间类型"><span class="toc-text">6.4 日期时间类型</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-5-列的默认值"><span class="toc-text">6.5 列的默认值</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-6-主键与自增"><span class="toc-text">6.6 主键与自增</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-7-列的删除与增加：（列的增删改）"><span class="toc-text">6.7 列的删除与增加：（列的增删改）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-8-视图：（存储的都是语句）"><span class="toc-text">6.8 视图：（存储的都是语句）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-9-引擎的概念"><span class="toc-text">6.9 引擎的概念</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-10-字符集与乱码问题"><span class="toc-text">6.10 字符集与乱码问题</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-11-索引"><span class="toc-text">6.11 索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-12-索引操作"><span class="toc-text">6.12 索引操作</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#七、常用函数"><span class="toc-text">七、常用函数</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#7-1-数学函数"><span class="toc-text">7.1 数学函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-2-聚合函数-常用于group-by从句的select查询中"><span class="toc-text">7.2 聚合函数(常用于group by从句的select查询中)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-3-字符串函数"><span class="toc-text">7.3 字符串函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-4-日期和时间函数"><span class="toc-text">7.4 日期和时间函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-5-加密函数"><span class="toc-text">7.5 加密函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-6-格式化函数"><span class="toc-text">7.6 格式化函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-7-类型转化函数"><span class="toc-text">7.7 类型转化函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-8-系统信息函数"><span class="toc-text">7.8 系统信息函数</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#八、Mysql十条常用语句"><span class="toc-text">八、Mysql十条常用语句</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#九、可视化管理数据"><span class="toc-text">九、可视化管理数据</span></a></li></ol>
  </div>
  




<div class="content content-post CENTER">
   <!-- canvas 彩带 -->
<canvas id="evanyou" width="1302" height="678" style="position: fixed;width: 100%;height: 100%;top: 0;left:0;z-index:-1;"></canvas>

<article id="post-mysql-base-review" class="article article-type-post" itemprop="blogPost">
  <header class="article-header" style="position:relative;">
    <h1 class="post-title">Mysql基础复习</h1>

    <div class="article-meta">
      <span>
        <i class="icon-calendar"></i>
        <span>2019.01.22</span>
      </span>

      
        <span class="article-author">
          <i class="icon-user"></i>
          <span>Poetry</span>
        </span>
      

      
  <span class="article-category">
    <i class="icon-list"></i>
    <a class="article-category-link" href="/categories/DataBase/">DataBase</a>
  </span>



      

      
      <i class="fa fa-eye"></i> 
        <span id="busuanzi_container_page_pv">
           &nbsp热度 <span id="busuanzi_value_page_pv">
           <i class="fa fa-spinner fa-spin"></i></span>℃
        </span>
      
      
       
          <span class="post-count">
            <i class="fa fa-file-word-o"></i>&nbsp
            <span>字数统计 8.9k字</span>
          </span>

          <span class="post-count">
            <i class="fa fa-columns"></i>&nbsp
            <span>阅读时长 39分</span>
          </span>
      
      
    </div>

    <i class="iconfont" id="toc-eye" style="display:inline-block;color:#b36619;position:absolute;top:0;right:0;cursor:pointer;
    font-size: 24px;">&#xe61c;</i>

  </header>

  <div class="article-content">
    
      <div id="container">
        <blockquote>
<p>好久没用<code>sql</code>，都忘得干干净净，翻阅以前的学习笔记，觉得有些可记录的点，放在这里以便备用查阅</p>
</blockquote>
<h2 id="一、环境搭建"><a href="#一、环境搭建" class="headerlink" title="一、环境搭建"></a>一、环境搭建</h2><blockquote>
<p><code>mac</code>安装<code>MySQL</code></p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">brew install mysql</span><br></pre></td></tr></table></figure>
<p><img src="https://poetries1.gitee.io/img-repo/2019/10/355.png" alt></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 启动</span></span><br><span class="line"></span><br><span class="line">mysql.server start</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment">#登录</span></span><br><span class="line">mysql -uroot</span><br></pre></td></tr></table></figure>
<h2 id="二、基础知识"><a href="#二、基础知识" class="headerlink" title="二、基础知识"></a>二、基础知识</h2><h3 id="1、数据库的连接"><a href="#1、数据库的连接" class="headerlink" title="1、数据库的连接"></a>1、数据库的连接</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 例子</span></span><br><span class="line">mysql -u root -p 123456 -h 127.0.0.1</span><br></pre></td></tr></table></figure>
<ul>
<li><code>-u</code> 用户名</li>
<li><code>-p</code> 密码</li>
<li><code>-h</code> <code>host</code>主机</li>
</ul>
<h3 id="2、库级知识"><a href="#2、库级知识" class="headerlink" title="2、库级知识"></a>2、库级知识</h3><blockquote>
<p>命令后面加上分号</p>
</blockquote>
<ul>
<li>显示数据库: <code>show databases;</code></li>
<li>选择数据库: <code>use dbname;</code></li>
<li>创建数据库: <code>create database dbname charset utf8;</code></li>
<li>删除数据库: <code>drop database dbname;</code></li>
</ul>
<h3 id="3、表级操作"><a href="#3、表级操作" class="headerlink" title="3、表级操作"></a>3、表级操作</h3><h4 id="3-1-显示库下面的表"><a href="#3-1-显示库下面的表" class="headerlink" title="3.1 显示库下面的表"></a>3.1 显示库下面的表</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">show tables;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-查看表的结构"><a href="#3-2-查看表的结构" class="headerlink" title="3.2 查看表的结构"></a>3.2 查看表的结构</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">desc tableName;</span><br></pre></td></tr></table></figure>
<h4 id="3-3-查看表的创建过程"><a href="#3-3-查看表的创建过程" class="headerlink" title="3.3 查看表的创建过程:"></a>3.3 查看表的创建过程:</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">show create table  tableName;</span><br></pre></td></tr></table></figure>
<h4 id="3-4-创建表"><a href="#3-4-创建表" class="headerlink" title="3.4 创建表"></a>3.4 创建表</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table tbName (</span><br><span class="line">列名称1　列类型　[列参数]　[not null default ],</span><br><span class="line">列名称N　列类型　[列参数]　[not null default ]</span><br><span class="line">) engine myisam/innodb charset utf8/gbk</span><br></pre></td></tr></table></figure>
<p><strong>例子</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table user (</span><br><span class="line">    id    int         auto_increment,</span><br><span class="line">    name  varchar(20) not null default <span class="string">''</span>,</span><br><span class="line">    age   tinyint unsigned not null default 0,</span><br><span class="line">    index id (id)</span><br><span class="line">)engine=innodb charset=utf8;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,</span></span><br><span class="line"><span class="comment"># charset 常用的有utf8,gbk;</span></span><br></pre></td></tr></table></figure>
<h4 id="3-5-修改表"><a href="#3-5-修改表" class="headerlink" title="3.5 修改表"></a>3.5 修改表</h4><p><strong>3.5.1    修改表之增加列</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName add 列名称１　列类型　[列参数]　[not null default ]　</span><br><span class="line"></span><br><span class="line"><span class="comment">#(add之后的旧列名之后的语法和创建表时的列声明一样)</span></span><br></pre></td></tr></table></figure>
<p><strong>3.5.2    修改表之修改列</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName change 旧列名  新列名  列类型　[列参数]　[not null default ]</span><br><span class="line"></span><br><span class="line"><span class="comment"># (注:旧列名之后的语法和创建表时的列声明一样)</span></span><br></pre></td></tr></table></figure>
<p><strong>3.5.3    修改表之减少列</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName drop 列名称;</span><br></pre></td></tr></table></figure>
<p><strong>3.5.4    修改表之增加主键</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName add primary key(主键所在列名);</span><br></pre></td></tr></table></figure>
<blockquote>
<p>例:<code>alter table goods add primary key(id)</code> 该例是把主键建立在<code>id</code>列上</p>
</blockquote>
<p><strong>3.5.5    修改表之删除主键</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName　drop primary key;</span><br></pre></td></tr></table></figure>
<p><strong>3.5.6    修改表之增加索引</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName add [unique|fulltext] index 索引名(列名);</span><br></pre></td></tr></table></figure>
<p><strong>3.5.7    修改表之删除索引</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">alter table tbName drop index 索引名;</span><br></pre></td></tr></table></figure>
<p><strong>3.5.8    清空表的数据</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">truncate tableName;</span><br></pre></td></tr></table></figure>
<h3 id="4、列类型讲解"><a href="#4、列类型讲解" class="headerlink" title="4、列类型讲解"></a>4、列类型讲解</h3><h4 id="4-1-列类型"><a href="#4-1-列类型" class="headerlink" title="4.1 列类型"></a>4.1 列类型</h4><ul>
<li><code>tinyint (0~255/-128~127)</code></li>
<li><code>smallint (0~65535/-32768~32767)</code></li>
<li><code>mediumint</code></li>
<li><code>int</code> </li>
<li><code>bigint</code></li>
</ul>
<p><strong>参数解释</strong></p>
<blockquote>
<p><code>unsigned</code> 无符号(不能为负)  <code>zerofill 0</code>填充  <code>M</code> 填充后的宽度</p>
</blockquote>
<ul>
<li>举例:</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">tinyint unsigned;</span><br><span class="line">tinyint(6) zerofill;</span><br></pre></td></tr></table></figure>
<h4 id="4-2-数值型"><a href="#4-2-数值型" class="headerlink" title="4.2 数值型"></a>4.2 数值型</h4><ul>
<li>浮点型:<code>float</code> <code>double</code></li>
<li>格式:<code>float(M,D)</code>  <code>unsigned\zerofill;</code></li>
</ul>
<h4 id="4-3-字符型"><a href="#4-3-字符型" class="headerlink" title="4.3 字符型"></a>4.3 字符型</h4><ul>
<li><code>char(m)</code> 定长</li>
<li><code>varchar(m)</code>变长</li>
<li><code>text</code></li>
</ul>
<table>
<thead>
<tr>
<th>列</th>
<th>实存字符i</th>
<th>实占空间</th>
<th>利用率</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>char(M)</code></td>
<td><code>0&lt;=i&lt;=M</code></td>
<td><code>M</code></td>
<td><code>i/m&lt;=100%</code></td>
</tr>
<tr>
<td><code>varchar(M)</code></td>
<td><code>0&lt;=i&lt;=M</code></td>
<td><code>i+1,2</code></td>
<td><code>i/i+1/2&lt;100%</code></td>
</tr>
</tbody>
</table>
<h4 id="4-4-日期时间类型"><a href="#4-4-日期时间类型" class="headerlink" title="4.4 日期时间类型"></a>4.4 日期时间类型</h4><ul>
<li><code>year</code>       <code>YYYY</code>    范围:<code>1901~2155</code>. 可输入值<code>2</code>位和<code>4</code>位(如<code>98</code>,<code>2012</code>)</li>
<li><code>date</code>       <code>YYYY-MM-DD</code> 如:<code>2010-03-14</code></li>
<li><code>time</code>       <code>HH:MM:SS</code>    如:<code>19:26:32</code></li>
<li><code>datetime</code>   <code>YYYY-MM-DD</code>  <code>HH:MM:SS</code> 如:<code>2010-03-14 19:26:32</code></li>
<li><code>timestamp</code>  <code>YYYY-MM-DD</code>  <code>HH:MM:SS</code> </li>
</ul>
<blockquote>
<p>特性:不用赋值,该列会为自己赋当前的具体时间</p>
</blockquote>
<h3 id="5、增删改查基本操作"><a href="#5、增删改查基本操作" class="headerlink" title="5、增删改查基本操作"></a>5、增删改查基本操作</h3><h4 id="5-1-插入数据"><a href="#5-1-插入数据" class="headerlink" title="5.1 插入数据"></a>5.1 插入数据</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">insert into 表名(col1,col2,……) values(val1,val2……); <span class="comment"># -- 插入指定列</span></span><br><span class="line"></span><br><span class="line">insert into 表名 values (,,,,); <span class="comment"># -- 插入所有列</span></span><br><span class="line"></span><br><span class="line">insert into 表名 values	<span class="comment"># -- 一次插入多行 </span></span><br><span class="line">(val1,val2……),</span><br><span class="line">(val1,val2……),</span><br><span class="line">(val1,val2……);</span><br></pre></td></tr></table></figure>
<h4 id="5-2-修改数据"><a href="#5-2-修改数据" class="headerlink" title="5.2 修改数据"></a>5.2 修改数据</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">update tablename </span><br><span class="line"></span><br><span class="line"><span class="built_in">set</span> </span><br><span class="line"></span><br><span class="line">col1=newval1,  </span><br><span class="line">col2=newval2,</span><br><span class="line">...</span><br><span class="line">...</span><br><span class="line">colN=newvalN</span><br><span class="line"><span class="built_in">where</span> 条件;</span><br></pre></td></tr></table></figure>
<h4 id="5-3-删除数据"><a href="#5-3-删除数据" class="headerlink" title="5.3 删除数据"></a>5.3 删除数据</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">delete from tablenaeme <span class="built_in">where</span> 条件;</span><br></pre></td></tr></table></figure>
<h4 id="5-4-select-查询"><a href="#5-4-select-查询" class="headerlink" title="5.4 select 查询"></a>5.4 select 查询</h4><ol>
<li>条件查询   <code>where</code></li>
</ol>
<ul>
<li>条件表达式的意义，表达式为真，则该行取出</li>
<li>比较运算符  <code>=</code> ，<code>!=</code>，<code>&lt; &gt;</code> <code>&lt;=</code> <code>&gt;=</code></li>
<li><code>like</code> , <code>not like</code> (‘<code>%</code>‘匹配任意多个字符,’<code>_</code>‘匹配任意单个字符) <code>in</code>, <code>not in</code> , <code>between and</code></li>
<li><code>is null</code> , <code>is not null</code></li>
</ul>
<ol start="2">
<li>分组 <code>group by</code> 一般要配合<code>5</code>个聚合函数使用 <code>max</code>, <code>min</code>, <code>sum</code>, <code>avg</code>, <code>count</code></li>
<li>筛选<code>having</code></li>
<li>排序<code>order by</code></li>
<li>限制<code>limit</code></li>
</ol>
<h3 id="6、连接查询"><a href="#6、连接查询" class="headerlink" title="6、连接查询"></a>6、连接查询</h3><h4 id="6-1-左连接"><a href="#6-1-左连接" class="headerlink" title="6.1 左连接"></a>6.1 左连接</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">.. left join .. on</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">table A left join table B on tableA.col1 = tableB.col2 ;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>例句:</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select 列名 from table A left join table B on tableA.col1 = tableB.col2</span><br></pre></td></tr></table></figure>
<h4 id="6-2-右链接"><a href="#6-2-右链接" class="headerlink" title="6.2 右链接"></a>6.2 右链接</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">right join</span><br></pre></td></tr></table></figure>
<h4 id="6-3-内连接"><a href="#6-3-内连接" class="headerlink" title="6.3 内连接"></a>6.3 内连接</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">inner join</span><br></pre></td></tr></table></figure>
<ul>
<li>左右连接都是以在左边的表的数据为准,沿着左表查右表.</li>
<li>内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集</li>
</ul>
<h3 id="7、子查询"><a href="#7、子查询" class="headerlink" title="7、子查询"></a>7、子查询</h3><blockquote>
<p><code>where</code> 型子查询:内层<code>sql</code>的返回值在<code>where</code>后作为条件表达式的一部分</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 例句: select * from tableA where colA = (select colB from tableB where ...);</span></span><br></pre></td></tr></table></figure>
<blockquote>
<p><code>from</code> 型子查询:内层<code>sql</code>查询结果,作为一张表,供外层的<code>sql</code>语句再次查询</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">例句:select * from (select * from ...) as tableName <span class="built_in">where</span> ....</span><br></pre></td></tr></table></figure>
<h3 id="8、字符集"><a href="#8、字符集" class="headerlink" title="8、字符集"></a>8、字符集</h3><ul>
<li>客户端<code>sql</code>编码 <code>character_set_client</code></li>
<li>服务器转化后的<code>sql</code>编码 <code>character_set_connection</code></li>
<li>服务器返回给客户端的结果集编码<code>character_set_results</code></li>
<li>快速把以上<code>3</code>个变量设为相同值: <code>set names</code> 字符集</li>
</ul>
<p><strong>存储引擎 engine=1\2</strong></p>
<ul>
<li><code>Myisam</code>  速度快 不支持事务 回滚</li>
<li><code>Innodb</code>  速度慢 支持事务,回滚</li>
</ul>
<p><strong>事务</strong></p>
<ul>
<li>开启事务  <code>start transaction</code></li>
<li>运行<code>sql;</code>      </li>
<li>提交,同时生效\回滚 <code>commit\rollback</code></li>
</ul>
<p><strong>触发器</strong></p>
<ul>
<li>触发器 <code>trigger</code></li>
<li>监视地点:表</li>
<li>监视行为:增 删 改</li>
<li>触发时间:<code>after\before</code></li>
<li>触发事件:增删改</li>
</ul>
<p><strong>创建触发器语法</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create trigger tgName</span><br><span class="line">after/before insert/delete/update </span><br><span class="line">on tableName</span><br><span class="line"><span class="keyword">for</span> each row</span><br><span class="line">sql; <span class="comment"># -- 触发语句</span></span><br></pre></td></tr></table></figure>
<ul>
<li>删除触发器:</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">drop trigger tgName;</span><br></pre></td></tr></table></figure>
<p><strong>索引</strong></p>
<ul>
<li>提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.</li>
<li>索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.</li>
<li>值越分散的列，索引的效果越好</li>
</ul>
<p><strong>索引类型</strong></p>
<ul>
<li><code>primary key</code>主键索引</li>
<li><code>index</code> 普通索引</li>
<li><code>unique index</code> 唯一性索引</li>
<li><code>fulltext index</code> 全文索引</li>
</ul>
<p><strong>综合练习:</strong></p>
<ul>
<li>连接上数据库服务器</li>
<li>创建一个<code>gbk</code>编码的数据库</li>
<li>建立商品表和栏目表,字段如下:</li>
</ul>
<p><strong>商品表:goods</strong></p>
<ul>
<li><code>goods_id</code>　–主键,</li>
<li><code>goods_name</code> – 商品名称</li>
<li><code>cat_id</code>  – 栏目<code>id</code></li>
<li><code>brand_id</code> – 品牌<code>id</code></li>
<li><code>goods_sn</code> – 货号</li>
<li><code>goods_number</code> – 库存量</li>
<li><code>shop_price</code>  – 价格</li>
<li><code>goods_desc</code>　–商品详细描述</li>
</ul>
<p><strong>栏目表:category</strong></p>
<ul>
<li>cat_id –主键 </li>
<li>cat_name – 栏目名称</li>
<li>parent_id – 栏目的父id</li>
</ul>
<blockquote>
<p>建表完成后,作以下操作:</p>
</blockquote>
<ul>
<li>删除goods表的goods_desc 字段,及货号字段</li>
<li>并增加字段:click_count  – 点击量</li>
<li>在goods_name列上加唯一性索引</li>
<li>在shop_price列上加普通索引</li>
<li>在clcik_count列上加普通索引</li>
<li>删除click_count列上的索引</li>
</ul>
<p><strong>对goods表插入以下数据:</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+</span><br><span class="line">| goods_id | goods_name                   | cat_id | brand_id | goods_sn  | goods_number | shop_price | click_count |</span><br><span class="line">+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+</span><br><span class="line">|        1 | KD876                        |      4 |        8 | ECS000000 |           10 |    1388.00 |           7 |</span><br><span class="line">|        4 | 诺基亚N85原装充电器          |      8 |        1 | ECS000004 |           17 |      58.00 |           0 |</span><br><span class="line">|        3 | 诺基亚原装5800耳机           |      8 |        1 | ECS000002 |           24 |      68.00 |           3 |</span><br><span class="line">|        5 | 索爱原装M2卡读卡器           |     11 |        7 | ECS000005 |            8 |      20.00 |           3 |</span><br><span class="line">|        6 | 胜创KINGMAX内存卡            |     11 |        0 | ECS000006 |           15 |      42.00 |           0 |</span><br><span class="line">|        7 | 诺基亚N85原装立体声耳机HS-82 |      8 |        1 | ECS000007 |           20 |     100.00 |           0 |</span><br><span class="line">|        8 | 飞利浦9@9v                   |      3 |        4 | ECS000008 |           17 |     399.00 |           9 |</span><br><span class="line">|        9 | 诺基亚E66                    |      3 |        1 | ECS000009 |           13 |    2298.00 |          20 |</span><br><span class="line">|       10 | 索爱C702c                    |      3 |        7 | ECS000010 |            7 |    1328.00 |          11 |</span><br><span class="line">|       11 | 索爱C702c                    |      3 |        7 | ECS000011 |            1 |    1300.00 |           0 |</span><br><span class="line">|       12 | 摩托罗拉A810                 |      3 |        2 | ECS000012 |            8 |     983.00 |          14 |</span><br><span class="line">|       13 | 诺基亚5320 XpressMusic       |      3 |        1 | ECS000013 |            8 |    1311.00 |          13 |</span><br><span class="line">|       14 | 诺基亚5800XM                 |      4 |        1 | ECS000014 |            4 |    2625.00 |           6 |</span><br><span class="line">|       15 | 摩托罗拉A810                 |      3 |        2 | ECS000015 |            3 |     788.00 |           8 |</span><br><span class="line">|       16 | 恒基伟业G101                 |      2 |       11 | ECS000016 |            0 |     823.33 |           3 |</span><br><span class="line">|       17 | 夏新N7                       |      3 |        5 | ECS000017 |            1 |    2300.00 |           2 |</span><br><span class="line">|       18 | 夏新T5                       |      4 |        5 | ECS000018 |            1 |    2878.00 |           0 |</span><br><span class="line">|       19 | 三星SGH-F258                 |      3 |        6 | ECS000019 |            0 |     858.00 |           7 |</span><br><span class="line">|       20 | 三星BC01                     |      3 |        6 | ECS000020 |           13 |     280.00 |          14 |</span><br><span class="line">|       21 | 金立 A30                     |      3 |       10 | ECS000021 |           40 |    2000.00 |           4 |</span><br><span class="line">|       22 | 多普达Touch HD               |      3 |        3 | ECS000022 |            0 |    5999.00 |          15 |</span><br><span class="line">|       23 | 诺基亚N96                    |      5 |        1 | ECS000023 |            8 |    3700.00 |          17 |</span><br><span class="line">|       24 | P806                         |      3 |        9 | ECS000024 |          148 |    2000.00 |          36 |</span><br><span class="line">|       25 | 小灵通/固话50元充值卡        |     13 |        0 | ECS000025 |            2 |      48.00 |           0 |</span><br><span class="line">|       26 | 小灵通/固话20元充值卡        |     13 |        0 | ECS000026 |            2 |      19.00 |           0 |</span><br><span class="line">|       27 | 联通100元充值卡              |     15 |        0 | ECS000027 |            2 |      95.00 |           0 |</span><br><span class="line">|       28 | 联通50元充值卡               |     15 |        0 | ECS000028 |            0 |      45.00 |           0 |</span><br><span class="line">|       29 | 移动100元充值卡              |     14 |        0 | ECS000029 |            0 |      90.00 |           0 |</span><br><span class="line">|       30 | 移动20元充值卡               |     14 |        0 | ECS000030 |            9 |      18.00 |           1 |</span><br><span class="line">|       31 | 摩托罗拉E8                   |      3 |        2 | ECS000031 |            1 |    1337.00 |           5 |</span><br><span class="line">|       32 | 诺基亚N85                    |      3 |        1 | ECS000032 |            1 |    3010.00 |           9 |</span><br><span class="line">+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+</span><br></pre></td></tr></table></figure>
<h2 id="三、查询知识"><a href="#三、查询知识" class="headerlink" title="三、查询知识"></a>三、查询知识</h2><blockquote>
<p>注:以下查询基于<code>ecshop</code>网站的商品表(<code>ecs_goods</code>)</p>
</blockquote>
<blockquote>
<p>在练习时可以只取部分列,方便查看.</p>
</blockquote>
<h3 id="3-1-基础查询-where的练习"><a href="#3-1-基础查询-where的练习" class="headerlink" title="3.1 基础查询 where的练习"></a>3.1 基础查询 where的练习</h3><blockquote>
<p>查出满足以下条件的商品</p>
</blockquote>
<h4 id="3-1-1-主键为32的商品"><a href="#3-1-1-主键为32的商品" class="headerlink" title="3.1.1 主键为32的商品"></a>3.1.1 主键为32的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,shop_price </span><br><span class="line">     from ecs_goods</span><br><span class="line">     <span class="built_in">where</span> goods_id=32;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-2-不属第3栏目的所有商品"><a href="#3-1-2-不属第3栏目的所有商品" class="headerlink" title="3.1.2 不属第3栏目的所有商品"></a>3.1.2 不属第3栏目的所有商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods</span><br><span class="line">     <span class="built_in">where</span> cat_id!=3;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-3-本店价格高于3000元的商品"><a href="#3-1-3-本店价格高于3000元的商品" class="headerlink" title="3.1.3 本店价格高于3000元的商品"></a>3.1.3 本店价格高于3000元的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods</span><br><span class="line">     <span class="built_in">where</span> shop_price &gt;3000;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-4-本店价格低于或等于100元的商品"><a href="#3-1-4-本店价格低于或等于100元的商品" class="headerlink" title="3.1.4 本店价格低于或等于100元的商品"></a>3.1.4 本店价格低于或等于100元的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods <span class="built_in">where</span> shop_price &lt;=100;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-5-取出第4栏目或第11栏目的商品-不许用or"><a href="#3-1-5-取出第4栏目或第11栏目的商品-不许用or" class="headerlink" title="3.1.5 取出第4栏目或第11栏目的商品(不许用or)"></a>3.1.5 取出第4栏目或第11栏目的商品(不许用or)</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods</span><br><span class="line">     <span class="built_in">where</span> cat_id <span class="keyword">in</span> (4,11);</span><br></pre></td></tr></table></figure>
<h4 id="3-1-6-取出100-lt-价格-lt-500的商品-不许用and"><a href="#3-1-6-取出100-lt-价格-lt-500的商品-不许用and" class="headerlink" title="3.1.6 取出100&lt;=价格&lt;=500的商品(不许用and)"></a>3.1.6 取出100&lt;=价格&lt;=500的商品(不许用and)</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods</span><br><span class="line">     <span class="built_in">where</span> shop_price between 100 and 500;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-7-取出不属于第3栏目且不属于第11栏目的商品-and-或not-in分别实现"><a href="#3-1-7-取出不属于第3栏目且不属于第11栏目的商品-and-或not-in分别实现" class="headerlink" title="3.1.7 取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)"></a>3.1.7 取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price from ecs_goods <span class="built_in">where</span> cat_id!=3 and cat_id!=11;</span><br><span class="line"></span><br><span class="line">select goods_id,cat_id,goods_name,shop_price from ecs_goods <span class="built_in">where</span> cat_id not <span class="keyword">in</span> (3,11);</span><br></pre></td></tr></table></figure>
<h4 id="3-1-8-取出价格大于100且小于300-或者大于4000且小于5000的商品"><a href="#3-1-8-取出价格大于100且小于300-或者大于4000且小于5000的商品" class="headerlink" title="3.1.8 取出价格大于100且小于300,或者大于4000且小于5000的商品"></a>3.1.8 取出价格大于100且小于300,或者大于4000且小于5000的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price from ecs_goods <span class="built_in">where</span> shop_price&gt;100 and shop_price &lt;300 or shop_price &gt;4000 and shop_price &lt;5000;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-9-取出第3个栏目下面价格-lt-1000或-gt-3000-并且点击量-gt-5的系列商品"><a href="#3-1-9-取出第3个栏目下面价格-lt-1000或-gt-3000-并且点击量-gt-5的系列商品" class="headerlink" title="3.1.9 取出第3个栏目下面价格&lt;1000或&gt;3000,并且点击量&gt;5的系列商品"></a>3.1.9 取出第3个栏目下面价格&lt;1000或&gt;3000,并且点击量&gt;5的系列商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods <span class="built_in">where</span></span><br><span class="line">cat_id=3 and (shop_price &lt;1000 or shop_price&gt;3000) and click_count&gt;5;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-10-取出第1个栏目下面的商品-注意-1栏目下面没商品-但其子栏目下有"><a href="#3-1-10-取出第1个栏目下面的商品-注意-1栏目下面没商品-但其子栏目下有" class="headerlink" title="3.1.10 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)"></a>3.1.10 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods</span><br><span class="line">     <span class="built_in">where</span> cat_id <span class="keyword">in</span> (2,3,4,5);</span><br></pre></td></tr></table></figure>
<h4 id="3-1-11-取出名字以”诺基亚”开头的商品"><a href="#3-1-11-取出名字以”诺基亚”开头的商品" class="headerlink" title="3.1.11 取出名字以”诺基亚”开头的商品"></a>3.1.11 取出名字以”诺基亚”开头的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods     <span class="built_in">where</span> goods_name like <span class="string">'诺基亚%'</span>;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-12-取出名字为”诺基亚Nxx”的手机"><a href="#3-1-12-取出名字为”诺基亚Nxx”的手机" class="headerlink" title="3.1.12 取出名字为”诺基亚Nxx”的手机"></a>3.1.12 取出名字为”诺基亚Nxx”的手机</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods  </span><br><span class="line">   <span class="built_in">where</span> goods_name like <span class="string">'诺基亚N__'</span>;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-13-取出名字不以”诺基亚”开头的商品"><a href="#3-1-13-取出名字不以”诺基亚”开头的商品" class="headerlink" title="3.1.13 取出名字不以”诺基亚”开头的商品"></a>3.1.13 取出名字不以”诺基亚”开头的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price from ecs_goos</span><br><span class="line">     <span class="built_in">where</span> goods_name not like <span class="string">'诺基亚%'</span>;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-14-取出第3个栏目下面价格在1000到3000之间-并且点击量-gt-5-“诺基亚”开头的系列商品"><a href="#3-1-14-取出第3个栏目下面价格在1000到3000之间-并且点击量-gt-5-“诺基亚”开头的系列商品" class="headerlink" title="3.1.14 取出第3个栏目下面价格在1000到3000之间,并且点击量&gt;5 “诺基亚”开头的系列商品"></a>3.1.14 取出第3个栏目下面价格在1000到3000之间,并且点击量&gt;5 “诺基亚”开头的系列商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price from ecs_goods <span class="built_in">where</span> </span><br><span class="line">cat_id=3 and shop_price&gt;1000 and shop_price &lt;3000 and click_count&gt;5 and goods_name like <span class="string">'诺基亚%'</span>;</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price  from ecs_goods <span class="built_in">where</span> </span><br><span class="line">shop_price between 1000 and 3000 and cat_id=3  and click_count&gt;5 and goods_name like <span class="string">'诺基亚%'</span>;</span><br></pre></td></tr></table></figure>
<h4 id="3-1-15-一道面试题"><a href="#3-1-15-一道面试题" class="headerlink" title="3.1.15 一道面试题"></a>3.1.15 一道面试题</h4><blockquote>
<p>有如下表和数组</p>
</blockquote>
<ul>
<li>把<code>num</code>值处于<code>[20,29]</code>之间,改为<code>20</code></li>
<li><code>num</code>值处于<code>[30,39]</code>之间的,改为<code>30</code></li>
</ul>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+</span><br><span class="line">| num  |</span><br><span class="line">+------+</span><br><span class="line">|    3 |</span><br><span class="line">|   12 |</span><br><span class="line">|   15 |</span><br><span class="line">|   25 |</span><br><span class="line">|   23 |</span><br><span class="line">|   29 |</span><br><span class="line">|   34 |</span><br><span class="line">|   37 |</span><br><span class="line">|   32 |</span><br><span class="line">|   45 |</span><br><span class="line">|   48 |</span><br><span class="line">|   52 |</span><br><span class="line">+------+</span><br></pre></td></tr></table></figure>
<h4 id="3-1-16-练习题"><a href="#3-1-16-练习题" class="headerlink" title="3.1.16 练习题:"></a>3.1.16 练习题:</h4><blockquote>
<p>把<code>good</code>表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’,</p>
</blockquote>
<ul>
<li>提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 。<code>ubstring()</code>, <code>concat()</code></li>
</ul>
<h3 id="3-2-分组查询group"><a href="#3-2-分组查询group" class="headerlink" title="3.2    分组查询group"></a>3.2    分组查询group</h3><h4 id="3-2-1-查出最贵的商品的价格"><a href="#3-2-1-查出最贵的商品的价格" class="headerlink" title="3.2.1 查出最贵的商品的价格"></a>3.2.1 查出最贵的商品的价格</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select max(shop_price) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-2-查出最大-最新-的商品编号"><a href="#3-2-2-查出最大-最新-的商品编号" class="headerlink" title="3.2.2 查出最大(最新)的商品编号"></a>3.2.2 查出最大(最新)的商品编号</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select max(goods_id) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-3-查出最便宜的商品的价格"><a href="#3-2-3-查出最便宜的商品的价格" class="headerlink" title="3.2.3 查出最便宜的商品的价格"></a>3.2.3 查出最便宜的商品的价格</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select min(shop_price) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-4-查出最旧-最小-的商品编号"><a href="#3-2-4-查出最旧-最小-的商品编号" class="headerlink" title="3.2.4 查出最旧(最小)的商品编号"></a>3.2.4 查出最旧(最小)的商品编号</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select min(goods_id) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-5-查询该店所有商品的库存总量"><a href="#3-2-5-查询该店所有商品的库存总量" class="headerlink" title="3.2.5 查询该店所有商品的库存总量"></a>3.2.5 查询该店所有商品的库存总量</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select sum(goods_number) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-6-查询所有商品的平均价"><a href="#3-2-6-查询所有商品的平均价" class="headerlink" title="3.2.6 查询所有商品的平均价"></a>3.2.6 查询所有商品的平均价</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select avg(shop_price) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-7-查询该店一共有多少种商品"><a href="#3-2-7-查询该店一共有多少种商品" class="headerlink" title="3.2.7 查询该店一共有多少种商品"></a>3.2.7 查询该店一共有多少种商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select count(*) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-2-8-查询每个栏目下面"><a href="#3-2-8-查询每个栏目下面" class="headerlink" title="3.2.8 查询每个栏目下面"></a>3.2.8 查询每个栏目下面</h4><ul>
<li>最贵商品价格</li>
<li>最低商品价格</li>
<li>商品平均价格</li>
<li>商品库存量</li>
<li>商品种类</li>
</ul>
<blockquote>
<p>提示:(<code>5</code>个聚合函数,<code>sum</code>, <code>avg</code>, <code>max</code>, <code>min</code>, <code>count</code>与<code>group</code>综合运用)<br><code>select cat_id,max(shop_price) from ecs_goods  group by cat_id;</code></p>
</blockquote>
<h3 id="3-3-having与group综合运用查询"><a href="#3-3-having与group综合运用查询" class="headerlink" title="3.3 having与group综合运用查询"></a>3.3 having与group综合运用查询</h3><h4 id="3-3-1-查询该店的商品比市场价所节省的价格"><a href="#3-3-1-查询该店的商品比市场价所节省的价格" class="headerlink" title="3.3.1 查询该店的商品比市场价所节省的价格"></a>3.3.1 查询该店的商品比市场价所节省的价格</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,market_price-shop_price as j </span><br><span class="line">     from ecs_goods ;</span><br></pre></td></tr></table></figure>
<h4 id="3-3-2-查询每个商品所积压的货款-提示-库存-单价"><a href="#3-3-2-查询每个商品所积压的货款-提示-库存-单价" class="headerlink" title="3.3.2 查询每个商品所积压的货款(提示:库存*单价)"></a>3.3.2 查询每个商品所积压的货款(提示:库存*单价)</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,goods_number*shop_price  from ecs_goods</span><br></pre></td></tr></table></figure>
<h4 id="3-3-3-查询该店积压的总货款"><a href="#3-3-3-查询该店积压的总货款" class="headerlink" title="3.3.3 查询该店积压的总货款"></a>3.3.3 查询该店积压的总货款</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select sum(goods_number*shop_price) from ecs_goods;</span><br></pre></td></tr></table></figure>
<h4 id="3-3-4-查询该店每个栏目下面积压的货款"><a href="#3-3-4-查询该店每个栏目下面积压的货款" class="headerlink" title="3.3.4 查询该店每个栏目下面积压的货款."></a>3.3.4 查询该店每个栏目下面积压的货款.</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;</span><br></pre></td></tr></table></figure>
<h4 id="3-3-5-查询比市场价省钱200元以上的商品及该商品所省的钱-where和having分别实现"><a href="#3-3-5-查询比市场价省钱200元以上的商品及该商品所省的钱-where和having分别实现" class="headerlink" title="3.3.5 查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)"></a>3.3.5 查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,market_price-shop_price  as k from ecs_goods</span><br><span class="line"><span class="built_in">where</span> market_price-shop_price &gt;200;</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,market_price-shop_price  as k from ecs_goods</span><br><span class="line">having k &gt;200;</span><br></pre></td></tr></table></figure>
<h4 id="3-3-6-查询积压货款超过2W元的栏目-以及该栏目积压的货款"><a href="#3-3-6-查询积压货款超过2W元的栏目-以及该栏目积压的货款" class="headerlink" title="3.3.6 查询积压货款超过2W元的栏目,以及该栏目积压的货款"></a>3.3.6 查询积压货款超过2W元的栏目,以及该栏目积压的货款</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id</span><br><span class="line">having k&gt;20000</span><br></pre></td></tr></table></figure>
<h4 id="3-3-7-where-having-group综合练习题"><a href="#3-3-7-where-having-group综合练习题" class="headerlink" title="3. 3.7 where-having-group综合练习题"></a>3. 3.7 where-having-group综合练习题</h4><blockquote>
<p>有如下表及数据</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+---------+-------+</span><br><span class="line">| name | subject | score |</span><br><span class="line">+------+---------+-------+</span><br><span class="line">| 张三 | 数学    |    90 |</span><br><span class="line">| 张三 | 语文    |    50 |</span><br><span class="line">| 张三 | 地理    |    40 |</span><br><span class="line">| 李四 | 语文    |    55 |</span><br><span class="line">| 李四 | 政治    |    45 |</span><br><span class="line">| 王五 | 政治    |    30 |</span><br><span class="line">+------+---------+-------+</span><br></pre></td></tr></table></figure>
<blockquote>
<p>要求:查询出2门及2门以上不及格者的平均成绩</p>
</blockquote>
<p>先查看每个人的平均成绩</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select name,avg(score) from stu group by name;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+------------+</span><br><span class="line">| name | avg(score) |</span><br><span class="line">+------+------------+</span><br><span class="line">| 张三 |    60.0000 |</span><br><span class="line">| 李四 |    50.0000 |</span><br><span class="line">| 王五 |    30.0000 |</span><br><span class="line">| 赵六 |    99.0000 |</span><br><span class="line">+------+------------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>看每个人挂科情况</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">mysql&gt; select name,score &lt; 60 from stu;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+------------+</span><br><span class="line">| name | score &lt; 60 |</span><br><span class="line">+------+------------+</span><br><span class="line">| 张三 |          0 |</span><br><span class="line">| 张三 |          1 |</span><br><span class="line">| 张三 |          1 |</span><br><span class="line">| 李四 |          1 |</span><br><span class="line">| 李四 |          1 |</span><br><span class="line">| 王五 |          1 |</span><br><span class="line">| 赵六 |          0 |</span><br><span class="line">| 赵六 |          0 |</span><br><span class="line">| 赵六 |          0 |</span><br><span class="line">+------+------------+</span><br><span class="line">9 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>计算每个人的挂科科目</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select name,sum(score &lt; 60) from stu group by name;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+-----------------+</span><br><span class="line">| name | sum(score &lt; 60) |</span><br><span class="line">+------+-----------------+</span><br><span class="line">| 张三 |               2 |</span><br><span class="line">| 李四 |               2 |</span><br><span class="line">| 王五 |               1 |</span><br><span class="line">| 赵六 |               0 |</span><br><span class="line">+------+-----------------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>同时计算每人的平均分</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select name,sum(score &lt; 60),avg(score) as pj from stu group by name;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+-----------------+---------+</span><br><span class="line">| name | sum(score &lt; 60) | pj      |</span><br><span class="line">+------+-----------------+---------+</span><br><span class="line">| 张三 |               2 | 60.0000 |</span><br><span class="line">| 李四 |               2 | 50.0000 |</span><br><span class="line">| 王五 |               1 | 30.0000 |</span><br><span class="line">| 赵六 |               0 | 99.0000 |</span><br><span class="line">+------+-----------------+---------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>利用<code>having</code>筛选挂科<code>2</code>门以上的.</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select name,sum(score &lt; 60) as gk ,avg(score) as pj from stu group by name having gk &gt;=2;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+------+---------+</span><br><span class="line">| name | gk   | pj      |</span><br><span class="line">+------+------+---------+</span><br><span class="line">| 张三 |    2 | 60.0000 |</span><br><span class="line">| 李四 |    2 | 50.0000 |</span><br><span class="line">+------+------+---------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h3 id="3-4、order-by-与-limit查询"><a href="#3-4、order-by-与-limit查询" class="headerlink" title="3.4、order by 与 limit查询"></a>3.4、order by 与 limit查询</h3><h4 id="3-4-1-按价格由高到低排序"><a href="#3-4-1-按价格由高到低排序" class="headerlink" title="3.4.1 按价格由高到低排序"></a>3.4.1 按价格由高到低排序</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;</span><br></pre></td></tr></table></figure>
<h4 id="3-4-2-按发布时间由早到晚排序"><a href="#3-4-2-按发布时间由早到晚排序" class="headerlink" title="3.4.2 按发布时间由早到晚排序"></a>3.4.2 按发布时间由早到晚排序</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,add_time from ecs_goods order by add_time;</span><br></pre></td></tr></table></figure>
<h4 id="3-4-3-接栏目由低到高排序-栏目内部按价格由高到低排序"><a href="#3-4-3-接栏目由低到高排序-栏目内部按价格由高到低排序" class="headerlink" title="3.4.3 接栏目由低到高排序,栏目内部按价格由高到低排序"></a>3.4.3 接栏目由低到高排序,栏目内部按价格由高到低排序</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,cat_id,goods_name,shop_price from ecs_goods</span><br><span class="line">     order by cat_id ,shop_price desc;</span><br></pre></td></tr></table></figure>
<h4 id="3-4-4-取出价格最高的前三名商品"><a href="#3-4-4-取出价格最高的前三名商品" class="headerlink" title="3.4.4 取出价格最高的前三名商品"></a>3.4.4 取出价格最高的前三名商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc <span class="built_in">limit</span> 3;</span><br></pre></td></tr></table></figure>
<h4 id="3-4-5-取出点击量前三名到前5名的商品"><a href="#3-4-5-取出点击量前三名到前5名的商品" class="headerlink" title="3.4.5 取出点击量前三名到前5名的商品"></a>3.4.5 取出点击量前三名到前5名的商品</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,click_count from ecs_goods order by click_count desc <span class="built_in">limit</span> 2,3;</span><br></pre></td></tr></table></figure>
<h3 id="3-5-连接查询"><a href="#3-5-连接查询" class="headerlink" title="3.5    连接查询"></a>3.5    连接查询</h3><h4 id="3-5-1-取出所有商品的商品名-栏目名-价格"><a href="#3-5-1-取出所有商品的商品名-栏目名-价格" class="headerlink" title="3.5.1 取出所有商品的商品名,栏目名,价格"></a>3.5.1 取出所有商品的商品名,栏目名,价格</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_name,cat_name,shop_price from </span><br><span class="line">ecs_goods left join ecs_category</span><br><span class="line">on ecs_goods.cat_id=ecs_category.cat_id;</span><br></pre></td></tr></table></figure>
<h4 id="3-5-2-取出第4个栏目下的商品的商品名-栏目名-价格"><a href="#3-5-2-取出第4个栏目下的商品的商品名-栏目名-价格" class="headerlink" title="3.5.2 取出第4个栏目下的商品的商品名,栏目名,价格"></a>3.5.2 取出第4个栏目下的商品的商品名,栏目名,价格</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_name,cat_name,shop_price from </span><br><span class="line">ecs_goods left join ecs_category</span><br><span class="line">on ecs_goods.cat_id=ecs_category.cat_id</span><br><span class="line"><span class="built_in">where</span> ecs_goods.cat_id = 4;</span><br></pre></td></tr></table></figure>
<h4 id="3-5-3-取出第4个栏目下的商品的商品名-栏目名-与品牌名"><a href="#3-5-3-取出第4个栏目下的商品的商品名-栏目名-与品牌名" class="headerlink" title="3.5.3 取出第4个栏目下的商品的商品名,栏目名,与品牌名"></a>3.5.3 取出第4个栏目下的商品的商品名,栏目名,与品牌名</h4><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_name,cat_name,brand_name from </span><br><span class="line">ecs_goods left join ecs_category</span><br><span class="line">on ecs_goods.cat_id=ecs_category.cat_id</span><br><span class="line">left join ecs_brand </span><br><span class="line">on ecs_goods.brand_id=ecs_brand.brand_id</span><br><span class="line"><span class="built_in">where</span> ecs_goods.cat_id = 4;</span><br></pre></td></tr></table></figure>
<h4 id="3-5-4-面试题"><a href="#3-5-4-面试题" class="headerlink" title="3.5.4 面试题"></a>3.5.4 面试题</h4><blockquote>
<p>根据给出的表结构按要求写出<code>SQL</code>语句。</p>
</blockquote>
<p><strong>Match 赛程表</strong></p>
<table>
<thead>
<tr>
<th>字段名称</th>
<th>字段类型</th>
<th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>matchID</code></td>
<td><code>int</code></td>
<td>主键</td>
</tr>
<tr>
<td><code>hostTeamID</code></td>
<td><code>int</code></td>
<td>主队的<code>ID</code></td>
</tr>
<tr>
<td><code>guestTeamID</code></td>
<td><code>int</code></td>
<td>客队的<code>ID</code></td>
</tr>
<tr>
<td><code>matchResult</code></td>
<td><code>varchar(20)</code></td>
<td>比赛结果，如（<code>2:0</code>）</td>
</tr>
<tr>
<td><code>matchTime</code></td>
<td><code>date</code></td>
<td>比赛开始时间</td>
</tr>
</tbody>
</table>
<p><strong>Team 参赛队伍表</strong></p>
<table>
<thead>
<tr>
<th>字段名称</th>
<th>字段类型</th>
<th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>teamID</code></td>
<td><code>int</code></td>
<td>主键</td>
</tr>
<tr>
<td><code>teamName</code></td>
<td><code>varchar(20)</code></td>
<td>队伍名称</td>
</tr>
</tbody>
</table>
<ul>
<li><code>Match</code>的<code>hostTeamID</code>与<code>guestTeamID</code>都与<code>Team</code>中的<code>teamID</code>关联</li>
<li>查出 <code>2006-6-1</code> 到<code>2006-7-1</code>之间举行的所有比赛，并且用以下形式列出：</li>
<li>拜仁  <code>2：0</code> 不来梅 <code>2006-6-21</code></li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select * from m;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+-----+------+------+------+------------+</span><br><span class="line">| mid | hid  | gid  | mres | matime     |</span><br><span class="line">+-----+------+------+------+------------+</span><br><span class="line">|   1 |    1 |    2 | 2:0  | 2006-05-21 |</span><br><span class="line">|   2 |    2 |    3 | 1:2  | 2006-06-21 |</span><br><span class="line">|   3 |    3 |    1 | 2:5  | 2006-06-25 |</span><br><span class="line">|   4 |    2 |    1 | 3:2  | 2006-07-21 |</span><br><span class="line">+-----+------+------+------+------------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select * from t;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+----------+</span><br><span class="line">| tid  | tname    |</span><br><span class="line">+------+----------+</span><br><span class="line">|    1 | 国安     |</span><br><span class="line">|    2 | 申花     |</span><br><span class="line">|    3 | 公益联队 |</span><br><span class="line">+------+----------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime</span><br><span class="line">    -&gt; from </span><br><span class="line">    -&gt; m left join t as t1</span><br><span class="line">    -&gt; on m.hid = t1.tid</span><br><span class="line">    -&gt; left join t as t2</span><br><span class="line">    -&gt; on m.gid = t2.tid;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+----------+------+------+----------+------------+</span><br><span class="line">| hid  | hname    | mres | gid  | gname    | matime     |</span><br><span class="line">+------+----------+------+------+----------+------------+</span><br><span class="line">|    1 | 国安     | 2:0  |    2 | 申花     | 2006-05-21 |</span><br><span class="line">|    2 | 申花     | 1:2  |    3 | 公益联队 | 2006-06-21 |</span><br><span class="line">|    3 | 公益联队 | 2:5  |    1 | 国安     | 2006-06-25 |</span><br><span class="line">|    2 | 申花     | 3:2  |    1 | 国安     | 2006-07-21 |</span><br><span class="line">+------+----------+------+------+----------+------------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h3 id="3-6、union查询"><a href="#3-6、union查询" class="headerlink" title="3.6、union查询"></a>3.6、union查询</h3><ul>
<li>把<code>ecs_comment</code>,<code>ecs_feedback</code>两个表中的数据,各取出<code>4</code>列,并把结果集<code>union</code>成一个结果集</li>
</ul>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">A表:</span><br><span class="line">+------+------+</span><br><span class="line">| id   | num  |</span><br><span class="line">+------+------+</span><br><span class="line">| a    |    5 |</span><br><span class="line">| b    |   10 |</span><br><span class="line">| c    |   15 |</span><br><span class="line">| d    |   10 |</span><br><span class="line">+------+------+</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">B表:</span><br><span class="line">+------+------+</span><br><span class="line">| id   | num  |</span><br><span class="line">+------+------+</span><br><span class="line">| b    |    5 |</span><br><span class="line">| c    |   15 |</span><br><span class="line">| d    |   20 |</span><br><span class="line">| e    |   99 |</span><br><span class="line">+------+------+</span><br></pre></td></tr></table></figure>
<p>要求查询出以下效果:</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+----------+</span><br><span class="line">| id   |    num   |</span><br><span class="line">+------+----------+</span><br><span class="line">| a    |        5 |</span><br><span class="line">| b    |       15 |</span><br><span class="line">| c    |       30 |</span><br><span class="line">| d    |       30 |</span><br><span class="line">| e    |       99 |</span><br><span class="line">+------+----------+</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">create table a (</span><br><span class="line">id char(1),</span><br><span class="line">num int</span><br><span class="line">) engine myisam charset utf8;</span><br><span class="line"></span><br><span class="line">insert into a values (<span class="string">'a'</span>,5),(<span class="string">'b'</span>,10),(<span class="string">'c'</span>,15),(<span class="string">'d'</span>,10);</span><br><span class="line"></span><br><span class="line">create table b (</span><br><span class="line">id char(1),</span><br><span class="line">num int</span><br><span class="line">) engine myisam charset utf8;</span><br><span class="line"></span><br><span class="line">insert into b values (<span class="string">'b'</span>,5),(<span class="string">'c'</span>,15),(<span class="string">'d'</span>,20),(<span class="string">'e'</span>,99);</span><br></pre></td></tr></table></figure>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; <span class="comment"># 合并 ,注意all的作用</span></span><br><span class="line">mysql&gt; select * from ta </span><br><span class="line">    -&gt; union all</span><br><span class="line">    -&gt; select * from tb;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+------+</span><br><span class="line">| id   | num  |</span><br><span class="line">+------+------+</span><br><span class="line">| a    |    5 |</span><br><span class="line">| b    |   10 |</span><br><span class="line">| c    |   15 |</span><br><span class="line">| d    |   10 |</span><br><span class="line">| b    |    5 |</span><br><span class="line">| c    |   15 |</span><br><span class="line">| d    |   20 |</span><br><span class="line">| e    |   99 |</span><br><span class="line">+------+------+</span><br></pre></td></tr></table></figure>
<p><strong>参考答案:</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; <span class="comment"># sum,group求和</span></span><br><span class="line">mysql&gt; select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">+------+----------+</span><br><span class="line">| id   | sum(num) |</span><br><span class="line">+------+----------+</span><br><span class="line">| a    |        5 |</span><br><span class="line">| b    |       15 |</span><br><span class="line">| c    |       30 |</span><br><span class="line">| d    |       30 |</span><br><span class="line">| e    |       99 |</span><br><span class="line">+------+----------+</span><br><span class="line">5 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h3 id="3-7、子查询"><a href="#3-7、子查询" class="headerlink" title="3.7、子查询:"></a>3.7、子查询:</h3><p><strong>查询出最新一行商品(以商品编号最大为最新,用子查询实现)</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name from </span><br><span class="line">     ecs_goods <span class="built_in">where</span> goods_id =(select max(goods_id) from ecs_goods);</span><br></pre></td></tr></table></figure>
<ul>
<li>查询出编号为<code>19</code>的商品的栏目名称(用左连接查询和子查询分别)</li>
<li>用<code>where</code>型子查询把<code>ecs_goods</code>表中的每个栏目下面最新的商品取出来</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,cat_id from ecs_goods <span class="built_in">where</span> goods_id <span class="keyword">in</span> (select max(goods_id) from ecs_goods group by cat_id);</span><br></pre></td></tr></table></figure>
<p><strong>用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;</span><br></pre></td></tr></table></figure>
<p><strong>用exists型子查询,查出所有有商品的栏目</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select * from category</span><br><span class="line"><span class="built_in">where</span> exists (select * from goods <span class="built_in">where</span> goods.cat_id=category.cat_id);</span><br></pre></td></tr></table></figure>
<p><strong>创建触发器:</strong></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">CREATE  trigger tg2</span><br><span class="line">after insert on ord</span><br><span class="line"><span class="keyword">for</span> each row</span><br><span class="line">update goods <span class="built_in">set</span> goods_number=goods_number-new.num <span class="built_in">where</span> id=new.gid</span><br><span class="line"></span><br><span class="line">CREATE trigger tg3</span><br><span class="line">after delete on ord</span><br><span class="line"><span class="keyword">for</span> each row</span><br><span class="line">update goods <span class="built_in">set</span> goods_number=good_number+old.num <span class="built_in">where</span> id=old.gid</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">CREATE  trigger tg4</span><br><span class="line">after update on ord</span><br><span class="line"><span class="keyword">for</span> each row</span><br><span class="line">update goods <span class="built_in">set</span> goods_number=goods_number+old.num-new.num <span class="built_in">where</span> id=old.gid</span><br></pre></td></tr></table></figure>
<h2 id="四、常用表管理语句"><a href="#四、常用表管理语句" class="headerlink" title="四、常用表管理语句"></a>四、常用表管理语句</h2><ul>
<li>设置字符编码 <code>set names gbk;</code></li>
<li>查看所有数据库：<code>show databases;</code></li>
<li>查看所有表：<code>show tables</code></li>
<li>查看表结构：<code>desc 表名/视图名</code></li>
<li>选择表 <code>use 表名;</code></li>
<li>查看建表过程：<code>show  create table  表名</code></li>
<li>查看建视图过程：<code>show create view 视图</code></li>
<li>查看所有详细表信息：<code>show table status\G(让结果显示好看一些)</code></li>
<li>查看某张表详细信息：<code>show table status where name=&#39;goods（表名）&#39;\G</code></li>
<li>删除表：<code>drop table 表名</code></li>
<li>删除视图：<code>drop view 视图名；</code></li>
<li>删除列：<code>alter table drop column 指定列</code></li>
<li>改表名：<code>rename table oldName to newName</code></li>
<li>更新表：<code>update 表名 set 字段</code></li>
<li>插入数据：<code>insert into 表名 value()</code></li>
<li>清空数据：<code>truncate 表名;(相当于删除表在重建)</code></li>
<li>写错语句退出:<code>\c</code></li>
<li>让结果显示好看一些:<code>\G</code></li>
</ul>
<h2 id="五、查询总结"><a href="#五、查询总结" class="headerlink" title="五、查询总结"></a>五、查询总结</h2><h3 id="5-1-insert"><a href="#5-1-insert" class="headerlink" title="5.1 insert"></a>5.1 insert</h3><ul>
<li><code>insert into 表名</code> 插入列与值要严格对应</li>
<li>数字不必加单引号 字符串必须加单引号</li>
<li>例子：<code>insert into test(age,name)values(10,&#39;小明&#39;);</code></li>
</ul>
<h3 id="5-2-update操作"><a href="#5-2-update操作" class="headerlink" title="5.2 update操作"></a>5.2 update操作</h3><figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// 例子：</span></span><br><span class="line">update user <span class="keyword">set</span> age=8 where name=lianying;</span><br><span class="line">//（注意where条件不加会影响所有行，需要小心）</span><br></pre></td></tr></table></figure>
<h3 id="5-3-delete操作"><a href="#5-3-delete操作" class="headerlink" title="5.3 delete操作"></a>5.3 delete操作</h3><ul>
<li>不可能针对某一列删除 要删必须一行</li>
<li><code>delete from 表名 where 添加</code></li>
<li><code>delete from user where uid=1;</code>（必须加上添加，否则全部数据删除）</li>
</ul>
<h3 id="5-4-select查找"><a href="#5-4-select查找" class="headerlink" title="5.4 select查找"></a>5.4 select查找</h3><ul>
<li><code>select * from 表名</code>（全部查出）</li>
<li><code>select uid,name from user where uid&gt;10;</code></li>
<li><code>select * from user where uid=11;</code></li>
</ul>
<h3 id="5-5-select查询模型（重要）"><a href="#5-5-select查询模型（重要）" class="headerlink" title="5.5 select查询模型（重要）"></a>5.5 select查询模型（重要）</h3><ul>
<li><code>select * from 表名 where 1</code>（<code>where</code>是一个表达式 为真则取出来 为假不取）</li>
<li>把列看成变量，既然是变量就能参与运。这个过程称为广义投影（比如：取出两列参与运算）也可以带到函数里面计算</li>
</ul>
<blockquote>
<p>注意：<code>NULL</code>：查询方法： <code>select * from test where name is （not）null</code></p>
</blockquote>
<h3 id="5-6-limit用法（做分页类能用到）"><a href="#5-6-limit用法（做分页类能用到）" class="headerlink" title="5.6 limit用法（做分页类能用到）"></a>5.6 limit用法（做分页类能用到）</h3><blockquote>
<p>限制取出条目（limit有两个参数 ：偏移量 取出的条目）</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select goods_id,goods_name,shop_price</span><br><span class="line">	-&gt; from goods</span><br><span class="line">	-&gt; order by shop_price desc</span><br><span class="line">	-&gt; <span class="built_in">limit</span>  0,3;</span><br></pre></td></tr></table></figure>
<h3 id="5-7-子句的查询陷阱"><a href="#5-7-子句的查询陷阱" class="headerlink" title="5.7 子句的查询陷阱"></a>5.7 子句的查询陷阱</h3><blockquote>
<p><code>5</code>种语句有严格的顺序，<code>where</code> ，<code>group by</code>,<code>having</code>,<code>order by</code>,<code>limit</code><br>不能颠倒顺序</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 例子:语句有严格的顺序</span></span><br><span class="line">mysql&gt; select id,sum(num) </span><br><span class="line">					-&gt; from</span><br><span class="line">					-&gt; (select * from a union select * from b) as temp</span><br><span class="line">					-&gt; group by id</span><br><span class="line">					-&gt; having sum(num)&gt;10</span><br><span class="line">					-&gt; order by sum(num) desc</span><br><span class="line">					-&gt; <span class="built_in">limit</span> 0,1;</span><br></pre></td></tr></table></figure>
<h3 id="5-8-子查询"><a href="#5-8-子查询" class="headerlink" title="5.8 子查询"></a>5.8 子查询</h3><p><strong>where字查询：（内层的查询结果作为外层的比较条件）</strong></p>
<ul>
<li>静态的：select goods_id,goods_name from goods where goods_id=32;</li>
<li>动态的：select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment">#取出每个栏目下最新的商品：</span></span><br><span class="line">select goods_id,cat_id,goods_name from goods <span class="built_in">where</span> goods_id <span class="keyword">in</span> (select max(goods_id) from goods group by cat_id);</span><br></pre></td></tr></table></figure>
<h3 id="5-9-from子查询"><a href="#5-9-from子查询" class="headerlink" title="5.9 from子查询"></a>5.9 from子查询</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment">#每个栏目下最新的商品：</span></span><br><span class="line">		mysql&gt; select goods_id,goods_name from (select * from goods <span class="built_in">where</span> 1 order by cat_id desc) as tmp</span><br><span class="line">			-&gt; group by cat_id;</span><br></pre></td></tr></table></figure>
<h3 id="5-10-exists子查询："><a href="#5-10-exists子查询：" class="headerlink" title="5.10 exists子查询："></a>5.10 exists子查询：</h3><figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment">#查询栏目下是否有商品</span></span><br><span class="line">		mysql&gt; select * from category</span><br><span class="line">			-&gt; <span class="built_in">where</span> exists(select * from goods <span class="built_in">where</span> goods.cat_id=category.cat_id)</span><br></pre></td></tr></table></figure>
<h3 id="5-11-内连接查询（重要）"><a href="#5-11-内连接查询（重要）" class="headerlink" title="5.11 内连接查询（重要）"></a>5.11 内连接查询（重要）</h3><blockquote>
<p>内连接是左右连接结果的交集</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select xxx from</span><br><span class="line">			table1 inner jion table2 on table1.xx=table2.xx</span><br><span class="line">			</span><br><span class="line">		</span><br><span class="line">mysql&gt; select boy.hid,boy.bname,girl.hid,girl.gname</span><br><span class="line">			-&gt; from</span><br><span class="line">			-&gt; boy inner join girl on boy.hid=girl.hid;</span><br></pre></td></tr></table></figure>
<h3 id="5-12-左连接特点"><a href="#5-12-左连接特点" class="headerlink" title="5.12 左连接特点"></a>5.12 左连接特点</h3><blockquote>
<p>以左表的数据为标准，去找右表的数据，查不到的为<code>NULL</code></p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment">#左连接</span></span><br><span class="line">mysql&gt; select boy.hid,boy.bname,girl.hid,girl.gname</span><br><span class="line">	-&gt; from</span><br><span class="line">	-&gt; boy left join girl on boy.hid=girl.hid;</span><br><span class="line"></span><br><span class="line"><span class="comment">#右连接</span></span><br><span class="line">mysql&gt; select boy.hid,boy.bname,girl.hid,girl.gname</span><br><span class="line">-&gt; from</span><br><span class="line">-&gt; boy right join girl on boy.hid=girl.hid;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">mysql&gt; select goods_id,cat_name,goods_name,shop_price</span><br><span class="line">	-&gt; from</span><br><span class="line">	-&gt; goods left join category on goods.cat_id= category.cat_id</span><br><span class="line">	-&gt; <span class="built_in">where</span> goods.cat_id=4;</span><br></pre></td></tr></table></figure>
<h3 id="5-13-union查询"><a href="#5-13-union查询" class="headerlink" title="5.13 union查询"></a>5.13 union查询</h3><blockquote>
<p>把<code>2</code>条或多条的额查询结果，合并成<code>1</code>个结果集</p>
</blockquote>
<ul>
<li><code>sql1 N行</code></li>
<li><code>sql2 M行</code></li>
<li><code>sql1 union sql2，N+M行</code></li>
</ul>
<blockquote>
<ul>
<li><code>union</code>语句必须满足一个条件：各语句取出的列数要相同</li>
<li><code>union</code>语句中不用写<code>order by</code> 因为<code>sql</code>合并后得到总的结果集可以<code>order by</code>字句<code>order by</code>失去意义</li>
<li>场景：<code>2</code>条语句，各自的<code>where</code>非常复杂，可以简化成简单的条件在<code>union</code></li>
<li>注意：使用<code>union</code>时，完全相等的行将会被合并。合并是比较耗时的操作，一般不让<code>union</code>合并，使用<code>union all</code>可以避免合并 对速度有提升</li>
</ul>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; select * from a</span><br><span class="line">	-&gt; union all <span class="comment">#union all 可以避免重复语句合并</span></span><br><span class="line">	-&gt; select * from b;</span><br><span class="line"></span><br><span class="line">mysql&gt; select goods_id,cat_id,goods_name,shop_price from goods <span class="built_in">where</span> cat_id=2</span><br><span class="line">	-&gt; union</span><br><span class="line">	-&gt; select goods_id,cat_id,goods_name,shop_price from goods <span class="built_in">where</span> cat_id=4;</span><br><span class="line">```			</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">## 六、建表总结</span></span><br><span class="line"></span><br><span class="line">```bash</span><br><span class="line">create table 表名 （</span><br><span class="line">	列1 列类型 [列属性 默认值]</span><br><span class="line">	列2 列类型 [列属性 默认值]</span><br><span class="line">	...</span><br><span class="line">	);</span><br><span class="line">	engine = 存储引擎</span><br><span class="line">	chartset = 字符集</span><br></pre></td></tr></table></figure>
<blockquote>
<p>建表过程：声明表头的过程，也就是声明列的过程</p>
</blockquote>
<ul>
<li>选择合理的列类型 合理的列宽度（即放下内容 又不浪费磁盘空间）</li>
<li>列选什么类型的列 列给什么样的属性</li>
<li>数值型–整形，浮点型，定点型</li>
<li>字符串型–<code>char</code> <code>varchar</code> <code>text</code></li>
<li>日期时间类型–<code>2012-12-13 14.25.36</code></li>
</ul>
<h3 id="6-1-整型列"><a href="#6-1-整型列" class="headerlink" title="6.1 整型列"></a>6.1 整型列</h3><table>
<thead>
<tr>
<th>类型：</th>
<th>字节：</th>
<th>最小值：</th>
<th>最大值：</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>bigint</code></td>
<td><code>8</code>字节</td>
<td><code>-9223372036854775808</code></td>
<td>18446744073709551615</td>
</tr>
<tr>
<td><code>int</code></td>
<td><code>4</code>字节</td>
<td><code>-2147483648</code></td>
<td><code>4294967295</code></td>
</tr>
<tr>
<td><code>mediunint</code></td>
<td><code>3</code>字节</td>
<td><code>-8388608</code></td>
<td><code>8388607</code></td>
</tr>
<tr>
<td><code>smallint</code></td>
<td>`2字节</td>
<td><code>-32768</code></td>
<td><code>3276</code>7</td>
</tr>
<tr>
<td><code>tinyint</code></td>
<td><code>1</code>字节</td>
<td><code>-128</code></td>
<td><code>127</code></td>
</tr>
</tbody>
</table>
<blockquote>
<p>整型列的可选参数</p>
</blockquote>
<ul>
<li><code>unsigned</code> 无符号，列的值从<code>0</code>开始不为负</li>
<li><code>zerofill M</code>（宽度）适合用于 学号 编码等固定宽度的数字，可以用0填充至固定宽度</li>
<li>学号：<code>1--0001</code></li>
<li>注意：<code>zerofill</code>属性默认决定是<code>unsigned</code></li>
</ul>
<h3 id="6-2-浮点列与定点列"><a href="#6-2-浮点列与定点列" class="headerlink" title="6.2 浮点列与定点列"></a>6.2 浮点列与定点列</h3><ul>
<li><code>float（M，D）</code> <code>M</code>是精度总位数 <code>D</code>代表小数点后面的位数</li>
<li><code>float/double</code> 范围区别和<code>decimal</code>相比：浮点数存储有精度的损失</li>
<li><code>decimal</code> 定点型更精确</li>
</ul>
<h3 id="6-3-字符型列"><a href="#6-3-字符型列" class="headerlink" title="6.3 字符型列"></a>6.3 字符型列</h3><ul>
<li><code>char(M)</code>–<code>char(10)</code>只能存<code>10</code>个字符</li>
<li><code>char</code>型:如果不够<code>M</code>个字符，内部会用空格补齐，取出时在把右侧空格删掉<ul>
<li>注意：这意味着右侧本身有空格将会丢失</li>
</ul>
</li>
<li><code>varchar(M)</code>–用多少占多少–自动扩展        </li>
<li><code>varchar</code>不会丢失空格</li>
<li>速度上：定长<code>char</code>快一些  在一定范围内用<code>char</code>定长寻址快 速度快</li>
<li><code>M</code>比较短<code>20</code>个以内用<code>char</code></li>
<li><code>text</code> 存大段文本</li>
<li><code>blob</code> 是二进制类型 用来存图像信息 音频等二进制信息<ul>
<li><code>blob</code>意义在于防止因为字符集的问题导致信息丢失</li>
</ul>
</li>
<li><code>enum</code>枚举类型：是定义好 值就在某几个枚举范围内<ul>
<li><code>gender emum(&#39;男&#39;,&#39;女&#39;) insert</code> 只能选其中之一</li>
</ul>
</li>
</ul>
<h3 id="6-4-日期时间类型"><a href="#6-4-日期时间类型" class="headerlink" title="6.4 日期时间类型"></a>6.4 日期时间类型</h3><ul>
<li><code>year</code>：存年份</li>
<li><code>date</code>:存年份日期2016-18</li>
<li><code>time</code>：存时分秒</li>
<li><code>datetime</code>:年月日时分秒</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; create table t8(</span><br><span class="line">	-&gt; ya year,</span><br><span class="line">	-&gt; dt date,</span><br><span class="line">	-&gt; tm time,</span><br><span class="line">	-&gt; dttm datetime);</span><br><span class="line">	-&gt; insert into t8 (ya,dt,tm) values(2015,<span class="string">'2015-12-18'</span>,<span class="string">'18:28:36'</span>);</span><br></pre></td></tr></table></figure>
<h3 id="6-5-列的默认值"><a href="#6-5-列的默认值" class="headerlink" title="6.5 列的默认值"></a>6.5 列的默认值</h3><ul>
<li><code>NULL</code>查询不方便</li>
<li><code>NULL</code>索引效率不高</li>
<li>实际中避免列的值为<code>NULL</code></li>
</ul>
<blockquote>
<p>如何避免：声明列<code>NOT NULL default</code>默认值</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">mysql&gt; create table t10(</span><br><span class="line">		-&gt; id int not null default 0,</span><br><span class="line">		-&gt; name char(10) not null default <span class="string">''</span></span><br><span class="line">		-&gt; );</span><br></pre></td></tr></table></figure>
<h3 id="6-6-主键与自增"><a href="#6-6-主键与自增" class="headerlink" title="6.6 主键与自增"></a>6.6 主键与自增</h3><ul>
<li>主键<code>primary key</code> 此列不重复，能区分每一行</li>
<li><code>primary key,auto_increment(一般那两个一起出现)</code></li>
<li>注意：一张表列只能有一列为<code>auto_increment</code> 且此列必须加索引（<code>index</code> <code>key</code>）</li>
<li>优化：<ul>
<li>定长（<code>char</code>）与变长（<code>varchar</code>）分离</li>
<li>常用与不常用列分离</li>
<li>能提高表的查询效率</li>
</ul>
</li>
</ul>
<h3 id="6-7-列的删除与增加：（列的增删改）"><a href="#6-7-列的删除与增加：（列的增删改）" class="headerlink" title="6.7 列的删除与增加：（列的增删改）"></a>6.7 列的删除与增加：（列的增删改）</h3><ul>
<li><code>alter  table 表名 add 列名 列类型 列属性</code> 默认在表的最后</li>
<li><code>alter  table  表名 drop column 指定列</code>–删除列</li>
<li><code>alter  table  表名 add 列名  列类型 列属性</code> [<code>after</code> 指定列的后面]</li>
<li><code>alter table  表名 change height(要修改的) shengao（被修改后的） smallint</code></li>
<li><code>alter table 表名 modify 列名</code> 要改成的新的属性</li>
</ul>
<h3 id="6-8-视图：（存储的都是语句）"><a href="#6-8-视图：（存储的都是语句）" class="headerlink" title="6.8 视图：（存储的都是语句）"></a>6.8 视图：（存储的都是语句）</h3><blockquote>
<p><code>view</code>被称为虚拟表，view是sql语句的查询结果（物理表的一个映射结果，物理表一改变，视图表也改变）</p>
</blockquote>
<p><strong>1. view好处</strong></p>
<ul>
<li>权限控制可用：<ul>
<li>比如某几个列允许用户查询，其他不允许</li>
<li>可通过视图开放其中一列或几列，起到权限控制作用</li>
</ul>
</li>
<li>简化复杂的查询</li>
<li>视图能更新？<ul>
<li>如果视图的每一行是与物理表一一对应的可以</li>
<li>view的行是由物理表多行经过计算得到的结果，view不可以更新</li>
</ul>
</li>
</ul>
<p><strong>2. 视图的algorithm</strong></p>
<ul>
<li>对于检查查询形成的view，在对view查询时，如order by where</li>
<li>可以把建视图语句+查视图的语句===合并成==&gt;查物理的语句</li>
<li>这种视图的算法叫merger（合并）</li>
</ul>
<h3 id="6-9-引擎的概念"><a href="#6-9-引擎的概念" class="headerlink" title="6.9 引擎的概念"></a>6.9 引擎的概念</h3><ul>
<li><code>mysql 5.0</code>以上默认的引擎是<code>innoDB</code>  一般建表时指定引擎</li>
<li><code>myisam</code>引擎存储的数据可以直接考出来拿去用</li>
<li><code>innDB</code>要把数据导出来</li>
</ul>
<blockquote>
<p><code>myisam</code>和<code>innDB</code>引擎区别</p>
</blockquote>
<table>
<thead>
<tr>
<th>mysiam</th>
<th>innDB</th>
</tr>
</thead>
<tbody>
<tr>
<td>批量插入的速度：</td>
<td>高</td>
<td>低</td>
</tr>
<tr>
<td>存储限制：</td>
<td>没有</td>
<td><code>64TB</code></td>
</tr>
</tbody>
</table>
<h3 id="6-10-字符集与乱码问题"><a href="#6-10-字符集与乱码问题" class="headerlink" title="6.10 字符集与乱码问题"></a>6.10 字符集与乱码问题</h3><ul>
<li>字符集、校对集(排序规则)、乱码</li>
<li>文字本来的字符集与展示的字符集不一致导致</li>
<li>客户端编码设置：<code>set names gbk/utf8;</code></li>
<li>表设置编码:<code>create table ()charset utf8;</code></li>
<li>服务器端<code>utf8/gbk</code> 都可</li>
<li>网页的话：<code>mate:charset=utf8;</code></li>
</ul>
<h3 id="6-11-索引"><a href="#6-11-索引" class="headerlink" title="6.11 索引"></a>6.11 索引</h3><ul>
<li>索引是数据的目录，能快速定位行数据的位置</li>
<li>索引提高了查询的速度，降低了增删改的速度，并非越多越好</li>
<li>一般在查询频率的列上加，而且在重复低列上加效果好</li>
<li><code>key</code> 普通索引</li>
<li><code>unique key</code> 唯一键</li>
<li><code>primary key</code> 主键索引</li>
<li>索引长度：建索引时，可以只索引列的前一部分的内容比如：前十个字符 <code>key email(email(10));</code></li>
<li>多列索引:就是把<code>2</code>列或者多列的值，看成一个整体，然后键索引</li>
<li>冗余索引：在某个列上可能存在多个索引</li>
</ul>
<h3 id="6-12-索引操作"><a href="#6-12-索引操作" class="headerlink" title="6.12 索引操作"></a>6.12 索引操作</h3><ul>
<li>查看索引：<code>show index from goods\G</code></li>
<li>删除索引：<code>alter table 表名 drop index 索引名</code><ul>
<li>或者：<code>drop index 索引名 on 表名</code></li>
</ul>
</li>
<li>添加：<code>alter table 表名 add [index \unqiue]索引名(列名)</code></li>
<li>添加主键索引：<code>alter table 表名 add primary key 列名</code></li>
<li>删除主键索引：<code>alter table 表名 drop primary key</code></li>
</ul>
<h2 id="七、常用函数"><a href="#七、常用函数" class="headerlink" title="七、常用函数"></a>七、常用函数</h2><h3 id="7-1-数学函数"><a href="#7-1-数学函数" class="headerlink" title="7.1 数学函数"></a>7.1 数学函数</h3><ul>
<li><code>abs(x)</code>   返回x的绝对值</li>
<li><code>bin(x)</code>   返回x的二进制（oct返回八进制，hex返回十六进制）</li>
<li><code>ceiling(x)</code>   返回大于x的最小整数值</li>
<li><code>exp(x)</code>   返回值<code>e</code>（自然对数的底）的<code>x</code>次方</li>
<li><code>floor(x)</code>   返回小于<code>x</code>的最大整数值</li>
<li><code>greatest(x1,x2,...,xn)</code>返回集合中最大的值</li>
<li><code>least(x1,x2,...,xn)</code>      返回集合中最小的值</li>
<li><code>ln(x)</code>                    返回x的自然对数</li>
<li><code>log(x,y)</code>返回<code>x</code>的以<code>y</code>为底的对数</li>
<li><code>mod(x,y)</code>                 返回x/y的模（余数）</li>
<li><code>pi()</code>返回<code>pi</code>的值（圆周率）</li>
<li><code>rand()</code>返回<code>０</code>到<code>1</code>内的随机值,可以通过提供一个参数(种子)使<code>rand()</code>随机数生成器生成一个指定的值。</li>
<li><code>round(x,y)</code>返回参数<code>x</code>的四舍五入的有<code>y</code>位小数的值</li>
<li><code>sign(x)</code>返回代表数字<code>x</code>的符号的值</li>
<li><code>sqrt(x)</code>返回一个数的平方根</li>
<li><code>truncate(x,y)</code>            返回数字<code>x</code>截短为<code>y</code>位小数的结果</li>
</ul>
<h3 id="7-2-聚合函数-常用于group-by从句的select查询中"><a href="#7-2-聚合函数-常用于group-by从句的select查询中" class="headerlink" title="7.2 聚合函数(常用于group by从句的select查询中)"></a>7.2 聚合函数(常用于group by从句的select查询中)</h3><ul>
<li><code>avg(col)</code>返回指定列的平均值</li>
<li><code>count(col)</code>返回指定列中非<code>null</code>值的个数</li>
<li><code>min(col)</code>返回指定列的最小值</li>
<li><code>max(col)</code>返回指定列的最大值</li>
<li><code>sum(col)</code>返回指定列的所有值之和</li>
<li><code>group_concat(col)</code>返回由属于一组的列值连接组合而成的结果</li>
</ul>
<h3 id="7-3-字符串函数"><a href="#7-3-字符串函数" class="headerlink" title="7.3 字符串函数"></a>7.3 字符串函数</h3><ul>
<li><code>ascii(char)</code>返回字符的<code>ascii</code>码值</li>
<li><code>bit_length(str)</code>返回字符串的比特长度</li>
<li><code>concat(s1,s2...,sn)</code>将s<code>1,s2...,sn</code>连接成字符串</li>
<li><code>concat_ws(sep,s1,s2...,sn)</code>将<code>s1,s2...,sn</code>连接成字符串，并用<code>sep</code>字符间隔</li>
<li><code>insert(str,x,y,instr)</code>将字符串<code>str</code>从第<code>x</code>位置开始，<code>y</code>个字符长的子串替换为字符串<code>instr</code>，返回结果</li>
<li><code>find_in_set(str,list)</code>分析逗号分隔的<code>list</code>列表，如果发现<code>str</code>，返回<code>str</code>在<code>list</code>中的位置</li>
<li><code>lcase(str)</code>或<code>lower(str)</code> 返回将字符串<code>str</code>中所有字符改变为小写后的结果</li>
<li><code>left(str,x)</code>返回字符串<code>str</code>中最左边的<code>x</code>个字符</li>
<li><code>length(s)</code>返回字符串<code>str</code>中的字符数</li>
<li><code>ltrim(str)</code> 从字符串<code>str</code>中切掉开头的空格</li>
<li><code>position(substr,str)</code> 返回子串<code>substr</code>在字符串<code>str</code>中第一次出现的位置</li>
<li><code>quote(str)</code> 用反斜杠转义<code>str</code>中的单引号</li>
<li><code>repeat(str,srchstr,rplcstr)</code>返回字符串<code>str</code>重复<code>x</code>次的结果</li>
<li><code>reverse(str) 返回颠倒字符串</code>str`的结果</li>
<li><code>right(str,x) 返回字符串</code>str<code>中最右边的</code>x`个字符</li>
<li><code>rtrim(str) 返回字符串</code>str`尾部的空格</li>
<li><code>strcmp(s1,s2)</code>比较字符串<code>s1</code>和<code>s2</code></li>
<li><code>trim(str)</code>去除字符串首部和尾部的所有空格</li>
<li><code>ucase(str)</code>或<code>upper(str)</code> 返回将字符串<code>str</code>中所有字符转变为大写后的结果</li>
</ul>
<h3 id="7-4-日期和时间函数"><a href="#7-4-日期和时间函数" class="headerlink" title="7.4 日期和时间函数"></a>7.4 日期和时间函数</h3><ul>
<li><code>curdate()</code>或<code>current_date()</code> 返回当前的日期</li>
<li><code>curtime()</code>或<code>current_time()</code> 返回当前的时间</li>
<li><code>date_add(date,interval int - keyword)</code>返回日期<code>date</code>加上间隔时间<code>int</code>的结果(<code>int</code>必须按照关键字进行格式化),如：<code>selectdate_add(current_date,interval 6 month);</code></li>
<li><code>date_format(date,fmt)</code>  依照指定的<code>fmt</code>格式格式化日期<code>date</code>值</li>
<li><code>date_sub(date,interval int - keyword)</code>返回日期<code>date</code>加上间隔时间<code>int</code>的结果(<code>int</code>必须按照关键字进行格式化),如：<code>selectdate_sub(current_date,interval 6 month);</code></li>
<li><code>dayofweek(date)</code>  返回date所代表的一星期中的第几天(<code>1~7</code>)</li>
<li><code>dayofmonth(date)</code>  返回date是一个月的第几天(<code>1~31</code>)</li>
<li><code>dayofyear(date)</code>  返回date是一年的第几天(<code>1~366</code>)</li>
<li><code>dayname(date)</code> 返回date的星期名，如：<code>select dayname(current_date);</code></li>
<li><code>from_unixtime(ts,fmt)</code> 根据指定的<code>fmt</code>格式，格式化<code>unix</code>时间戳<code>ts</code></li>
<li><code>hour(time)</code>  返回time的小时值<code>(0~23)</code></li>
<li><code>minute(time)</code>  返回time的分钟值<code>(0~59)</code></li>
<li><code>month(date)</code>  返回<code>date</code>的月份值<code>(1~12)</code></li>
<li><code>monthname(date)</code> 返回<code>date</code>的月份名，如：<code>select monthname(current_date);</code></li>
<li><code>now()</code>   返回当前的日期和时间</li>
<li><code>quarter(date)</code>  返回<code>date</code>在一年中的季度<code>(1~4)</code>，如<code>select quarter(current_date);`</code></li>
<li><code>week(date)</code>   返回日期<code>date</code>为一年中第几周(<code>0~53</code>)</li>
<li><code>year(date)</code>   返回日期<code>date</code>的年份(<code>1000~9999</code>)</li>
</ul>
<p><strong>一些示例</strong></p>
<ul>
<li>获取当前系统时间：</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select from_unixtime(unix_timestamp());</span><br><span class="line"></span><br><span class="line">select extract(year_month from current_date);</span><br><span class="line"></span><br><span class="line">select extract(day_second from current_date);</span><br><span class="line"></span><br><span class="line">select extract(hour_minute from current_date);</span><br></pre></td></tr></table></figure>
<ul>
<li>返回两个日期值之间的差值(月数)：</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select period_diff(200302,199802);</span><br></pre></td></tr></table></figure>
<ul>
<li>在<code>mysql</code>中计算年龄：</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select date_format(from_days(to_days(now())-to_days(birthday)),<span class="string">'%y'</span>)+0 as age from employee;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>这样，如果<code>brithday</code>是未来的年月日的话，计算结果为<code>0</code>。</p>
</blockquote>
<p>下面的<code>sql</code>语句计算员工的绝对年龄，即当<code>birthday</code>是未来的日期时，将得到负值</p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">select date_format(now(), <span class="string">'%y'</span>) - date_format(birthday, <span class="string">'%y'</span>) -(date_format(now(), <span class="string">'00-%m-%d'</span>) &lt;date_format(birthday, <span class="string">'00-%m-%d'</span>)) as age from employee</span><br></pre></td></tr></table></figure>
<h3 id="7-5-加密函数"><a href="#7-5-加密函数" class="headerlink" title="7.5 加密函数"></a>7.5 加密函数</h3><ul>
<li><code>aes_encrypt(str,key)</code>  返回用密钥<code>key</code>对字符串<code>str</code>利用高级加密标准算法加密后的结果，调用<code>aes_encrypt</code>的结果是一个二进制字符串，以<code>blob</code>类型存储</li>
<li><code>aes_decrypt(str,key)</code> 返回用密钥<code>key</code>对字符串<code>str</code>利用高级加密标准算法解密后的结果</li>
<li><code>decode(str,key)</code>  使用<code>key</code>作为密钥解密加密字符串<code>str</code></li>
<li><code>encrypt(str,salt)</code>   使用<code>unixcrypt()</code>函数，用关键词<code>salt</code>(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串<code>str</code></li>
<li><code>encode(str,key)</code>  使用key作为密钥加密字符串str，调用<code>encode()</code>的结果是一个二进制字符串，它以<code>blob</code>类型存储</li>
<li><code>md5()</code>   计算字符串<code>str</code>的<code>md5</code>校验和</li>
<li><code>password(str)</code>  返回字符串<code>str</code>的加密版本，这个加密过程是不可逆转的，和<code>unix</code>密码加密过程使用不同的算法。</li>
<li><code>sha()</code>  计算字符串<code>str</code>的安全散列算法(<code>sha</code>)校验和</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 示例：</span></span><br><span class="line">select encrypt(<span class="string">'root'</span>,<span class="string">'salt'</span>);</span><br><span class="line">select encode(<span class="string">'xufeng'</span>,<span class="string">'key'</span>);</span><br><span class="line">select decode(encode(<span class="string">'xufeng'</span>,<span class="string">'key'</span>),<span class="string">'key'</span>);<span class="comment">#加解密放在一起</span></span><br><span class="line">select aes_encrypt(<span class="string">'root'</span>,<span class="string">'key'</span>);</span><br><span class="line">select aes_decrypt(aes_encrypt(<span class="string">'root'</span>,<span class="string">'key'</span>),<span class="string">'key'</span>);</span><br><span class="line">select md5(<span class="string">'123456'</span>);</span><br><span class="line">select sha(<span class="string">'123456'</span>);</span><br></pre></td></tr></table></figure>
<h3 id="7-6-格式化函数"><a href="#7-6-格式化函数" class="headerlink" title="7.6 格式化函数"></a>7.6 格式化函数</h3><ul>
<li><code>date_format(date,fmt)</code>  依照字符串<code>fmt</code>格式化日期<code>date</code>值</li>
<li><code>format(x,y)</code>  把<code>x</code>格式化为以逗号隔开的数字序列，<code>y</code>是结果的小数位数</li>
<li><code>inet_aton(ip)</code>  返回<code>ip</code>地址的数字表示</li>
<li><code>inet_ntoa(num)</code>   返回数字所代表的<code>ip</code>地址</li>
<li><code>time_format(time,fmt)</code> 依照字符串<code>fmt</code>格式化时间<code>time</code>值</li>
<li>其中最简单的是<code>format()</code>函数，它可以把大的数值格式化为以逗号间隔的易读的序列。</li>
</ul>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line"># 示例：</span><br><span class="line"></span><br><span class="line">select format(34234.34323432,3);</span><br><span class="line"></span><br><span class="line">select date_format(now(),&apos;%w,%d %m %y %r&apos;);</span><br><span class="line"></span><br><span class="line">select date_format(now(),&apos;%y-%m-%d&apos;);</span><br><span class="line"></span><br><span class="line">select date_format(19990330,&apos;%y-%m-%d&apos;);</span><br><span class="line"></span><br><span class="line">select date_format(now(),&apos;%h:%i %p&apos;);</span><br><span class="line"></span><br><span class="line">select inet_aton(&apos;10.122.89.47&apos;);</span><br><span class="line"></span><br><span class="line">select inet_ntoa(175790383);</span><br></pre></td></tr></table></figure>
<h3 id="7-7-类型转化函数"><a href="#7-7-类型转化函数" class="headerlink" title="7.7 类型转化函数"></a>7.7 类型转化函数</h3><blockquote>
<p>为了进行数据类型转化，<code>mysql</code>提供了<code>cast()</code>函数，它可以把一个值转化为指定的数据类型。类型有：<code>binary</code>,<code>char</code>,<code>date</code>,<code>time</code>,<code>datetime</code>,<code>igned</code>,<code>unsigned</code></p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 示例：</span></span><br><span class="line"></span><br><span class="line">select cast(now() as signed <span class="built_in">integer</span>),curdate()+0;</span><br><span class="line">select <span class="string">'f'</span>=binary <span class="string">'f'</span>,<span class="string">'f'</span>=cast(<span class="string">'f'</span> as binary);</span><br></pre></td></tr></table></figure>
<h3 id="7-8-系统信息函数"><a href="#7-8-系统信息函数" class="headerlink" title="7.8 系统信息函数"></a>7.8 系统信息函数</h3><ul>
<li><code>database()</code>  返回当前数据库名</li>
<li><code>benchmark(count,expr)</code> 将表达式<code>expr</code>重复运行<code>count</code>次</li>
<li><code>connection_id()</code>   返回当前客户的连接<code>id</code></li>
<li><code>found_rows()</code>   返回最后一个<code>select</code>查询进行检索的总行数</li>
<li><code>user()</code>或<code>system_user()</code>  返回当前登陆用户名</li>
<li><code>version()</code>   返回<code>mysql</code>服务器的版本</li>
</ul>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line"><span class="comment"># 示例：</span></span><br><span class="line"></span><br><span class="line">select database(),version(),user();</span><br><span class="line"></span><br><span class="line"><span class="comment">#该例中,mysql计算log(rand()*pi())表达式9999999次。</span></span><br><span class="line">selectbenchmark(9999999,<span class="built_in">log</span>(rand()*pi()));</span><br></pre></td></tr></table></figure>
<h2 id="八、Mysql十条常用语句"><a href="#八、Mysql十条常用语句" class="headerlink" title="八、Mysql十条常用语句"></a>八、Mysql十条常用语句</h2><p><strong>1. 链接到数据库服务器</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">mysql -h 地址 -u root -p 密码</span><br></pre></td></tr></table></figure>
<p><strong>2. 查看所有库</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">show databases;</span><br></pre></td></tr></table></figure>
<p><strong>3. 选库</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">use 库名</span><br></pre></td></tr></table></figure>
<p><strong>4. 查看库下面的表</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">show tables;</span><br></pre></td></tr></table></figure>
<p><strong>5. 建表</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">create table msg&#123;</span><br><span class="line">    id int auto_increment primary key，</span><br><span class="line">	content varcha(200)，</span><br><span class="line">	pubtime int</span><br><span class="line">&#125;charset utf8;</span><br></pre></td></tr></table></figure>
<p><strong>6. 告诉服务器你的字符集：set names gbk/utg8;</strong></p>
<p><strong>7. 添加数据</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">insert into msg(id,content,pubtime) values(1,&apos;哈哈哈哈&apos;,13445);</span><br></pre></td></tr></table></figure>
<p><strong>8. 查询所有数据</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">select * from msg;</span><br></pre></td></tr></table></figure>
<p><strong>9. 按id查询</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">select * from where id = 2...</span><br></pre></td></tr></table></figure>
<p><strong>10. 快速清空表</strong></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">truncate 表名</span><br></pre></td></tr></table></figure>
<h2 id="九、可视化管理数据"><a href="#九、可视化管理数据" class="headerlink" title="九、可视化管理数据"></a>九、可视化管理数据</h2><blockquote>
<p>一般为了方便管理数据，我们都需要用到可视化工具</p>
</blockquote>
<ul>
<li><a href="https://www.navicat.com.cn/download/navicat-for-mysql" target="_blank" rel="noopener">navicat-for-mysql</a></li>
</ul>
<blockquote>
<p>这里提供一份数据表，供学习使用，<a href="https://blog.csdn.net/qq_33699659/article/details/79261661" target="_blank" rel="noopener">导入sql数据到navicat</a></p>
</blockquote>
<blockquote>
<p><a href="http://blog.poetries.top/sql/mysql-table.sql">http://blog.poetries.top/sql/mysql-table.sql</a></p>
</blockquote>

      </div>
    
  </div>

</article>

<button class="assist-btn2 circle" id="assist_btn2" title="点亮屏幕" style="left: 27px; top: 152px;">
  <i class="iconfont" style="display:inline-block;color:red;width:20px;height:20px;">&#xe61d;</i>
</button>
<button class="assist-btn1 circle" id="assist_btn1" title="关闭屏幕亮度" style="left: 27px; top: 152px;">
  <i class="iconfont toc-title" style="display:inline-block;color:red;width:20px;height:20px;">&#xe61d;</i>
</button>


<script src="//cdn.bootcss.com/jquery/3.1.1/jquery.min.js"></script>	

<script src="https://my.openwrite.cn/js/readmore.js" type="text/javascript"></script>
<script>
  const btw = new BTWPlugin();
  btw.init({
    id: "container",
    blogId: "22699-1592137983091-414",
    name: "前端进阶之旅",
    qrcode: "https://poetries1.gitee.io/img-repo/2020/06/qrcode.jpg",
    keyword: "3a3b3c",
  });
</script>

<script type="text/javascript">

// white theme
var body = {color: "#555", background: "#000"};
var a_tag = {color: "#222"};
var header = { background: "#222"};
var logo_line_i = {background: "#222"};
// var post_code = {background: "#eee", color: "#222"};

function switch_theme() {
 $("body").css(body);
 $("a:not('.links-of-author-item a, .site-state-item a, .site-state-posts a, .feed-link a, .motion-element a, .post-tags a, .show-commit-cls a, #donate_board a')").css(a_tag);
 $(".header, .footer").css(header);
 $(".logo-line-before i, .logo-line-after i").css(logo_line_i);
 //$(".post code").css(post_code);
 $("#idhyt-surprise-ball #idhyt-surprise-ball-animation .drag").css(a_tag);
 $(".post-title-link, .posts-expand .post-meta, .post-comments-count, .disqus-comment-count, .post-category a, .post-nav-next a, .post-nav-item a").css(a_tag);
 
 // $("code").css({color: '#c5c8c6', background: '#1d1f21'});
 //$("#assist_btn1").hide(1500);
}

$(function () {
$("#assist_btn2").css("display","none");
 $("#assist_btn1").click(function() {
     switch_theme();
$("div#toc.toc-article").css({
 "background":"#eaeaea",
 "opacity":1
});
$(".toc-article ol").show();
$("#toc.toc-article .toc-title").css("color","#a98602");
$("#assist_btn1").css("display","none");
$("#assist_btn2").css("display","block");
 });
$("#assist_btn2").click(function() {
$("#assist_btn2").css("display","none");
$("#assist_btn1").css("display","block");
$("body").css("background","url(http://www.miaov.com/static/ie/images/news/bg.png)")
     $(".header, .footer").css("background","url(http://www.miaov.com/static/ie/images/news/bg.png)")
$(".toc-article ol").toggle(1000);
 });
});


//背景随机

var Y, O, E, L, B, C, T, z, N, S, A, I;
!function() {
var e = function() {
for (O.clearRect(0, 0, L, B), T = [{
x: 0,
y: .7 * B + C
}, {
x: 0,
y: .7 * B - C
}]; T[1].x < L + C;) t(T[0], T[1])
}, t = function(e, t) {
O.beginPath(), O.moveTo(e.x, e.y), O.lineTo(t.x, t.y);
var n = t.x + (2 * I() - .25) * C,
 r = a(t.y);
O.lineTo(n, r), O.closePath(), N -= S / -50, O.fillStyle = "#" + (127 * A(N) + 128 << 16 | 127 * A(N + S / 3) + 128 << 8 | 127 * A(N + S / 3 * 2) + 128).toString(16), O.fill(), T[0] = T[1], T[1] = {
 x: n,
 y: r
}
}, a = function n(e) {
var t = e + (2 * I() - 1.1) * C;
return t > B || t < 0 ? n(e) : t
};
Y = document.getElementById("evanyou"), O = Y.getContext("2d"), E = window.devicePixelRatio || 1, L = window.innerWidth, B = window.innerHeight, C = 90, z = Math, N = 0, S = 2 * z.PI, A = z.cos, I = z.random, Y.width = L * E, Y.height = B * E, O.scale(E, E), O.globalAlpha = .6, document.onclick = e, document.ontouchstart = e, e()
}()

   
$("#toc-eye").click(function(){
$("#toc.toc-article").toggle(1000);
});

</script>


   
  <div class="text-center donation">
    <div class="inner-donation">
      <span class="btn-donation">支持一下</span>
      <div class="donation-body">
        <div class="tip text-center">扫一扫，支持poetries</div>
        <ul>
        
          <li class="item">
            
              <span>微信扫一扫</span>
            
            <img src="/images/weixin.jpg" alt="">
          </li>
        
          <li class="item">
            
              <span>支付宝扫一扫</span>
            
            <img src="/images/zhifubao.jpg" alt="">
          </li>
        
        </ul>
      </div>
    </div>
  </div>


   
  <div class="box-prev-next clearfix">
    <a class="show pull-left" href="/2019/01/10/ionic3-summary/">
        <i class="icon icon-angle-left"></i>
    </a>
    <a class="show pull-right" href="/2019/01/22/mongodb-review-1/">
        <i class="icon icon-angle-right"></i>
    </a>
  </div>




</div>


  <a id="backTop" class="back-top">
    <i class="icon-angle-up"></i>
  </a>




  <div class="modal" id="modal">
  <span id="cover" class="cover hide"></span>
  <div id="modal-dialog" class="modal-dialog hide-dialog">
    <div class="modal-header">
      <span id="close" class="btn-close">关闭</span>
    </div>
    <hr>
    <div class="modal-body">
      <ul class="list-toolbox">
        
          <li class="item-toolbox">
            <a
              class="CIRCLE"
              href="/archives/"
              rel="noopener noreferrer"
              target="_self"
              >
              博客
            </a>
          </li>
        
          <li class="item-toolbox">
            <a
              class="CIRCLE"
              href="/categories/"
              rel="noopener noreferrer"
              target="_self"
              >
              分类
            </a>
          </li>
        
          <li class="item-toolbox">
            <a
              class="CIRCLE"
              href="/tags/"
              rel="noopener noreferrer"
              target="_self"
              >
              标签
            </a>
          </li>
        
          <li class="item-toolbox">
            <a
              class="CIRCLE"
              href="/search/"
              rel="noopener noreferrer"
              target="_self"
              >
              搜索
            </a>
          </li>
        
          <li class="item-toolbox">
            <a
              class="CIRCLE"
              href="/link/"
              rel="noopener noreferrer"
              target="_self"
              >
              友链
            </a>
          </li>
        
          <li class="item-toolbox">
            <a
              class="CIRCLE"
              href="/about/"
              rel="noopener noreferrer"
              target="_self"
              >
              关于
            </a>
          </li>
        
      </ul>

    </div>
  </div>
</div>



  
      <div class="fexo-comments comments-post">
    

    

    
    

    

    
    

    

<!-- Gitalk评论插件通用代码 -->
<div id="gitalk-container"></div>

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">
<script src="https://cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.min.js"></script>
<script>
const gitalk = new Gitalk({
  clientID: '5567a2c4abb858009d96',
  clientSecret: 'b9039ec056cf5c2346b3cdb63308a28c163f91e5',
  repo: 'poetries.github.io',
  owner: 'poetries',
  // 在这里设置一下截取前50个字符串, 这是因为 github 对 label 的长度有了要求, 如果超过
  // 50个字符串则会报错.
  // id: location.pathname.split('/').pop().substring(0, 49),
  id: location.pathname,
  admin: ['poetries'],
  // facebook-like distraction free mode
  distractionFreeMode: false
})
gitalk.render('gitalk-container')
</script>
<!-- Gitalk代码结束 -->



  </div>

  

  <script type="text/javascript">
  function loadScript(url, callback) {
    var script = document.createElement('script')
    script.type = 'text/javascript';

    if (script.readyState) { //IE
      script.onreadystatechange = function() {
        if (script.readyState == 'loaded' ||
          script.readyState == 'complete') {
          script.onreadystatechange = null;
          callback();
        }
      };
    } else { //Others
      script.onload = function() {
        callback();
      };
    }

    script.src = url;
    document.getElementsByTagName('head')[0].appendChild(script);
  }

  window.onload = function() {
    loadScript('/js/bundle.js?235683', function() {
      // load success
    });
  }
</script>


  <!-- 页面点击小红心 -->
  <script type="text/javascript" src="/js/clicklove.js"></script>
 
  
</body>
</html>
